Google Sheets – Historic currency conversion rates

Software:

This is slightly off-topic in this blog, but since this subject has seriously baffled me, I decided to document and share the solution.

Getting a currency conversion rate in into a Google Sheets expression is fairly simple:

The expression:

``GoogleFinance("CURRENCY:USDILS")``

Simply returns the current conversion rate, in this case between US Dollars – “USD” to Israeli Shekel – “ILS”, expressed as the term “USDILS”.

So what’s the problem?
When using the GoogleFinance function to retrieve a historic conversion value like so:

``GoogleFinance("CURRENCY:USDILS","price","1/1/2012")``

the function doesn’t return the numeric value of the price, but instead, creates a table (see image below):

This seems like a nice feature.. so what’s the problem?
If you need to use the conversion rate within a larger scope calculation, the return value will be the string “Date” instead of a numeric value representing the conversion rate, and that will cause an error:

``````330*index(GoogleFinance("CURRENCY:USDILS","price","1/1/2012"),2,2)
``````

So.. what’s the solution?
The solution is to use the index function to retrieve the wanted value from the table returned by GoogleFinance.
In this case index(weird_surprising_tabel,2,2) returns the wanted value:

``330*index(GoogleFinance("CURRENCY:USDILS","price","1/1/2012"),2,2)``

That’s it.
Hope you’ll find this useful! ðŸ™‚

P.S.
I do use Google Sheets for subjects directly related to CG work, and I should definitely bring some examples of that here in this blog.

Using Color Lookup Tables (CLUTs)

Color Lookup Tables – CLUTs (also “Color LUTs“) are a method of storing and reusing complex linear color transformations*.
CLUTs have the advantages of being supported by many video and image processing software packages, and also the ability to be calculated in real-time on the GPU, costing very little computing resources.
* More simple, daily use terms can be: “color styles”, or “color corrections”

CLUTs are used in the movie production industry to perform color conversions of images acquired from different sources for monitoring and editing purposes, and also for testing, applying and sharing different creative color styles across different departments, and stages of the production.
Examples of common CLUT file formats are *.3DL and *.CUBE

Why is this called a “3D” or “Cube” Lookup Table?

The reason CLUTs are referred to as “3D” color lookup tables or “Cube LUT” is that they store the effect of color operations as linear transformations of a 3D cubic space.
To understand this we have to imagine RGB color encoding as a 3D space with the R, G and B values of each color being coordinates in this cubic color space.
This means that the color correction operations we perform to create a color style, like adding contrast, saturation, warming the hues etc. are all defined as a function that for every color coordinate in the RGB color cube space defines the new coordinate where the corrected or stylized color is found.
The term Lookup table means that the new color values don’t have to be calculated every time because they have been pre-calculated and stored in a table of values.
3D CLUTs are often processed and stored as 3D Cubic textures like this example generated with Blackmagic Fusion of a 32 x 32 x 32 value CLUT.
Imagine the little 32 x 32 square patches all stacked one upon the other, that would create a 32 x 32 x 32 RGB color cube, with which color transformations can be stored by simply applying them on this texture:

Working with CLUTs:

In this post we’ll go trough the process of creating and using a CLUT in some popular creative software packages.
* Note that there are many other software packages that support creation and usage of CLUTs, the process should be similar.

Steps shown in the following software:
Blackmagic Design Fusion 9

In this example our source image with which the CLUT will be designed is an interior scene modeled with Blender 2.82 and rendered with Cycles Render Engine with “Filmic” tone-mapping applied, saved as a PNG file.
* I usually save Linear unclamped 32bit float EXR files as the raw output from render engines, because this is the format that provides the most freedom to manipulate and process rendered images and animation, but from my experience CLUTS don’t work well on linear unclamped color, for that reason I usually apply them at a later stage of the image development (usually after applying tone-mapping to the image).
This is why I saved the file directly as a tone-mapped PNG for this example.

Creating a CLUT in Adobe Photoshop:

For this example, a greenish-contrasty-desaturated color style is created in Photoshop by applying color adjustment layers to the image.
In this case Color Balance, Vibrance, and Curves.
* You can use different numbers and combinations of color adjustments

The new Color Style is now exported to CLUT files:

In the Export Color Lookup Tables dialog allows naming the CLUT, adding a description, setting a quality for the color transform it will define, and selecting the wanted CLUT file formats that will be written.
After clicking OK the CLUT files will be saved in a chosen location.

Note:
Saving the CLUT in the Presets\3DLUTs folder (found in the Adobe Photoshop installation folder) will allow reusing the CLUT as a preset look available by drop-down selection without having to locate the file each time.

Applying a CLUT in Adobe Photoshop:

In the Color Lookup adjustment properties, open the 3DLUT File drop-down, choose Load 3D LUT, and locate the CLUT file you saved:

The original image now has the same color style we created earlier, but this time it’s applied by only a single Color Lookup adjustment layer:

An example of the same CLUT applied to a different image:

Applying a CLUT in Adobe After Effects:

Add a Util > Apply Color LUT effect to a layer,
In the Effect Controls window, click Choose LUT and locate the wanted CLUT file:

Applying a CLUT in Adobe Premiere:

1. Select the image/video clip in the timeline.
2. Switch to the Color UI tab to get access to the Lumetri Color controls on the right.
3. In the Creative section of the Lumetri Color controls, open the Look dropdown, choose Browse and locate the wanted CLUT file.

Applying a CLUT in Blackmagic Design Fusion:

Add a LUT >File LUT node to the image source.
In the File LUT properties, click the browse button and locate the wanted CLUT file:

Creating a CLUT in Blackmagic Design Fusion:

* See the numbered nodes in the flow graph below

1. Source image/video on which the CLUT is designed.
2. A LUT Cube Creator node, generating default neutral 3D CLUT data in the form of a Color Cube map.
3. The nodes creating the actual color style (in this case a Color Corrector and Color Curve nodes) are operating on the LUT Cube Creator node’s output.
4. A LUT Cube Apply node is applying the stylized CLUT data to the image/video for previewing purpose (displayed on the right viewer)
5. A LUT Cube Analyzer node generates CLUT data from the styled LUT Cube Creator data, and allows saving it to disk as a CLUT file.
* Choose a location and click Write File to save the CLUT file.