Data Studio
5.1K members online now
5.1K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Bug: Importing a data from Google Sheets source with currency conversion based on historic date

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Summary

 

Using the Google sheets connector it is not possible to visualise data in Google Data Studio where the source of the data involves, directly or indirectly, a currency conversion based on a historic data using the GOOGLEFINANCE() function.

 

Reproduction Steps:

 

  1. Create a google sheets spreadsheet with columns: date | original currency | converted currency
  2. Use the formula =GOOGLEFINANCE("<original currency><converted currency", "close", <date>) to convert the values for each row.
  3. Create a Google Data Studio report with that sheet as a data source
  4. Create a table widget with:
    1. dimension: date
    2. metric 1: original currency
    3. metric 2: converted currency

 

Expected: 

 

The converted currency column contains the same values as the google sheet

 

Happens:

 

The converted currency contains the original currency values. The conversion is not recognised by sheets. 

 

Here is an example sheet: https://docs.google.com/spreadsheets/d/1o-EdqeobTmnin0LVgqdUvugCVguzbLSjrgXWtr-0Tcs/edit#gid=0

Here is an example Data Studio Report: https://datastudio.google.com/org//reporting/0B9riI7R22nBMeVVHSzVxOG1CS1U/page/OjlD

 

Working problem

 

In my actual working sheet where I discovered the problem, converted values do not even register in Google Data Studio. Any row in the original data source that was involved directly or indirectly with a currency conversion using historic exchange rates just is not picked up by the data studio.

 

I have a sheet of a combination of transactions from multiple bank accounts in multiple currencies. All currencies are converted to EUR based on the closing rate of the day so an overall picture can be obtained in a Data Studio report. Currently we only see the result of the transactions in the original currency. The results in Google Sheets is completely accurate.

 

Any fix / workaround to get this working would be much appreciated.

Bug: Importing a data from Google Sheets source with currency conversion based on historic date

Visitor ✭ ✭ ✭
# 2
Visitor ✭ ✭ ✭

This is a sheets only workaround (not tested by us):

 

https://www.en.advertisercommunity.com/t5/Data-Studio/Google-finance-not-displaying-correctly-in-Dat...

Workbook 1) Use the google finance to extract the dates you want. Then by formulas populate a table that is easy to extract to the next workbook.

Workbook 2) using the IMPORTRANGE formula i extract the table built in the previous step. I then convert all the data in that table to its correct version through for example VALUE(). This sheet i then read into google data studio and at this point i get the numbers i wanted.

 

Having had a deep read through the docs we discovered that the results from historic Google Finance formulas are not accessible outside of the visible spreadsheet. 

 

Google Finance docs - https://support.google.com/docs/answer/3093281?hl=en

"

  • Historical data cannot be downloaded or accessed via the Sheets API or Apps Script.  If you attempt to do so, you will see a #N/A error in place of the values in the corresponding cells of your spreadsheet.

"