Historical Currency Conversion Using GoogleFinance

[intapiuser]

 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. 

And then to the Trigger page.

And create a new trigger. 

For this use case, I'll set the Macro to run daily between 1 and 2 am. This way when my build starts at 3 am, it will refer to the updated data. 

Step 5: Connect to the data source and build

We can now use this to convert EUR to USD based on historical exchange rates!