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 (in SQL):
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 (in SQL):
=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 (in SQL):
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!
