Historical Currency Conversion Using GoogleFinance

Analytical Need

I have historical transactional data starting from 2012. Some are recorded in USD, others in EUR. I need to convert these values to USD based on the exchange rate on the date of the transaction.

My Elasticube is built on a daily basis at 3 am. I would like to convert EUR to USD based on the close price of the currency security.

Modeling Challenge

I do not have a record of historical conversion rates and am not sure how to configure the Currency Conversion Data Connector to get exchange rates for a range of days, not just a single date.

Solution

The GoogleFinance function in Google Sheets can generate this information for us very easily. We will create a sheet and refer to it as a data source.

Step 1: Create a Google Sheet and Generate the Array

The syntax for the GoogleFinance function is:

GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])

In our case, we want the EURUSD close price starting in 2012 and ending today. We'd like this information on a daily basis.

For this, the synaxis:

=GoogleFinance("CURRENCY:EURUSD","close","1/1/2012",DATEVALUE(today()), "DAILY")

This function does not return a table; it returns an array. If we try and connect to this array as a data source, it will not be brought into our cube correctly because it is not a table.

Step 2: Create a Results Sheet

We need to convert this array to a table. We will do this by automating a process of paste as values to a new sheet.

First, create a Results USD sheet and make sure the first column is set to a Date format.

Step 3: Write or Record a Paste-as-Values Macro

Next, write or record a Macro to copy and paste the full columns to the new sheet.

My Macro script is:

function PasteasValuestoResultsUSD() {
 var spreadsheet = SpreadsheetApp.getActive();
 spreadsheet.setActiveSheet(spreadsheet.getSheetByName('GoogleFinance EURUSD'), true);
 spreadsheet.getRange('A1:B').activate();
 spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Results USD'), true);
 spreadsheet.getRange('A1').activate();
 spreadsheet.getRange('GoogleFinance EURUSD!A:B').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

Step 4: Set Up a Daily Trigger to Automate the Process on a Daily Basis

Navigate to the Script Editor.