cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member

 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")
 
2019-02-13_09h54_33.png
 
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.
 
2019-02-13_10h26_49.png
 
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. 
2019-02-13_10h10_54.png
 
And then to the Trigger page.
 
2019-02-13_10h11_29.png
 
And create a new trigger. 
2019-02-13_10h16_10.png
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. 
 
2019-02-13_10h29_24.png
 
Step 5: Connect to the data source and build
2019-02-13_10h22_43.png
 
We can now use this to convert EUR to USD based on historical exchange rates!
Rate this article:
Version history
Last update:
‎02-23-2024 09:12 AM
Updated by: