cancel
Showing results for 
Search instead for 
Did you mean: 
Community_Admin
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 (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")

 

Community_Admin_0-1634475160649.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.

Community_Admin_1-1634475160709.png

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. 

Community_Admin_2-1634475201578.png

And then to the Trigger page.

Community_Admin_3-1634475201632.png

And create a new trigger. 

Community_Admin_4-1634475201580.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. 

Community_Admin_5-1634475201709.png

Step 5: Connect to the data source and build

Community_Admin_6-1634475201610.png

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

Version history
Last update:
‎10-17-2021 05:54 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Need additional support?:

Community Support Request