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

Google finance not displaying correctly in Data studio

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hello,

 

I select, through Google finance, the price for a stock today and compares it to the price yesterday. This difference and the development in % is what I am trying to pickup in Data studio.

If i write the values manually it works just fine, but as soon as the source is the Google finance formula it doesn't come through to Data studio as values anymore. When I try and adjust this in the import it still doesn't show up as values.

 

Anyone who knows any solution for this issue?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Per-Erik S
July

Google finance not displaying correctly in Data studio

[ Edited ]
Visitor ✭ ✭ ✭
# 2
Visitor ✭ ✭ ✭

Found a solution in using two different workbooks:

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.

This isn't very practical, but it solves the problem until a better solution is presented.

View solution in original post

Marked as Best Answer.
Solution
Accepted by topic author Per-Erik S
July

Google finance not displaying correctly in Data studio

[ Edited ]
Visitor ✭ ✭ ✭
# 2
Visitor ✭ ✭ ✭

Found a solution in using two different workbooks:

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.

This isn't very practical, but it solves the problem until a better solution is presented.

Google finance not displaying correctly in Data studio

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

We had come up against this one too. 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.

 

You may want to double check the values you get in Google Data Studio when reading from a sheet that has data originating from Google Finance formulas. We had used exchange rate formulas and although we had no problems viewing in Data Studio, the exchange rates were all being treated as 1=1 by Data Studio. 

 

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.

"

From our experience this meant any consumer that wasn't the sheet itself was not able to access the values correctly. Interesting that you found a workaround. 

 

Google finance not displaying correctly in Data studio

Visitor ✭ ✭ ✭
# 4
Visitor ✭ ✭ ✭

Hopefully it can work in your case as well Smiley Happy

I am not sure but I guess that technically it should be possible to do a importrange within the same document and perhaps then remove the need for two sheets - however I haven't had a chance to try that just yet.

Google finance not displaying correctly in Data studio

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

I can confirm that this workaround works. I used importRange within the same Workbook from one sheet to another. Then used the second sheet to get the data into my dashboard. Works! I wish this could be done directly in DS.

Google finance not displaying correctly in Data studio

Visitor ✭ ✭ ✭
# 6
Visitor ✭ ✭ ✭

Well atleast that removes the need to have extra sheets just laying around. 

 

As it is now I do all my google finance formulas in different books for keeping it simple and light and then I extract the data into the same workbook and then only work towards that one workbook when I am in DS.

 

but yeah, glad the solution works.