8/31/2016
Google Finance Data in Tableau 10 using Google Sheets Connection
NOTE: Post updated 9/19/2017 thanks to a solution provided by Jann-Boje Meinecke using the IMPORTRANGE() function. The IMPORTHTML() function can be used instead of the GOOGLEFINANCE() function and Adam Crahen has excellent instructions on how to set that up posted here
A few weeks ago I posted instructions on Connecting Google Sheets with Tableau 10. This technique connects data from Google Sheets to Tableau and updates it daily. In the previous post I imported data into Googe Sheets using the IMPORTHTML function and returned data from a table. In this example, I am going to use a built-in function in Google Sheets for historical financial data which comes from Google Finance. The build instructions are very similar.
Step 1 - Build the Google Sheet:
To start, create a Google Sheet with the financial data
1.) Create new Google Sheet, example "Stock Data"
2.) Put a column name in cell A1 for "Symbol" and enter the stock symbol in cell A2. In this example, I used DATA which is the ticker symbol for Tableau Software.
3.) Create a GOOGLEFINANCE formula in cell B1. The syntax is:
=GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])
These formulas will read the stock symbol you entered in cell A2 and return the historical data for the stock symbol from Google Finance. The formula in B1 is: =if(today()=today(),GOOGLEFINANCE(A2,"all",TODAY()-365,TODAY()),"")
Note: To utilize the auto refresh in Google Sheets on the GOOGLEFINANCE function I added an IF statement using today() = today() which should fire off the formula in accordance with the Google Sheets setting (#6 below).
4.) If you need to use the symbol in your viz then create a formula for the symbol in cell A3 and copy and past the stock symbol in column A down to end of the data (optional). The formula I used in A3 is: =if(ISBLANK(B3),"", "DATA")
This populates the symbol for any cell that is returned from Google Finance in Column B. Copy and paste down through Row 365 to insure that the symbol is populated on the sheet for anything with data. Note: Stock data is only returned for weekdays, so using the formula above doesn't actually return 365 rows.
5.) Click Share in the top right-hand corner, set the setting so this document can be viewed by others and copy the URL.
6.) Click "File" -> "Spreadsheet setting" and set the "recalculate" to "On change and every day". This will keep the data refreshed, reading directly from Google Finance.
You now have a Google Sheet with the historical price and volume for Tableau's stock. My finished sheet is here.
Step 2 - Build a Second Google Sheet to Read from the First (updated 9/19/2017
NOTE - This step was added to solve problem importing into Tableau)
1.) Create new Google Sheet with a different name than the first one, example "Stock Data for Import"
2.) Use the IMPORTRANGE() function to read the data from the Google Sheet you created in step 1.
The formula in cell A1 uses the link copied above in Step 1 should look something like this: =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1hyOk9nbnPRU1TepMKpPctlDxf1krTvZbb8UI7gRgYEc/edit", "Sheet1!A1:G489")
3) Click Share in the top right-hand corner, set the setting so this document can be viewed by others and copy the URL.
4.) Click "File" -> "Spreadsheet setting" and set the "recalculate" to "On change and every day". This will keep the data refreshed, reading directly from Google Finance.
Step 4 - Connect to Tableau:
1.) This is super simple. Just open Tableau and select a new database source as Google Sheets.
2.) Enter your credentials and select the second Google Sheet you created in Step 2 or enter the URL that you copied in Step 2.
3.) The data will now load into Tableau.
4.) Build your viz with the data from the Google Sheet.
Step 3 - Publish to Tableau Public and Keep Data in Sync:
1.) When you publish to Tableau Public, make sure to check the box "Keep my data in sync with Google Sheets and embed my Google credentials". This will keep the data updated, refreshing daily.
2.) Once on Tableau Public you can request an update of the data by clicking "Request Update".
Below is a visualization putting the pieces together (link to Tableau Public here). This shows Tableau's stock price on the top line chart and the volume of the daily trading on the bottom chart, a very common view for visualizing stocks.
This is another example of seamless integration of Google Sheets in Tableau 10. Here is a list of all of the attributes availabe in the GOOGLEFINANCE function.
A few examples of attributes:
"price" - Realtime price quote, delayed by up to 20 minutes (realtime data)
"marketcap" - The market capitalization of the stock.
"pe" - The price/earnings ratio (realtime data)
"eps" - The earnings per share (realtime data)
"high52" - The 52-week high price (realtime data)
"low52" - The 52-week low price (realtime data)
"open" - The opening price for the specified date(s) (historical data)
"close" - The closing price for the specified date(s) (historical data)
"low" - The low price for the specified date(s) (historical data)
"volume" - The volume for the specified date(s) (historical data)
"all" - The volume for the specified date(s) (historical data)
"closeyest" - The previous day's closing price (mutual fund data)
"return52" - Fifty-two-week (annual) total return
"return260" - 260-week (5-year) total return (mutual fund data)
"incomedividend" - The amount of the most recent cash distribution (mutual fund data)
"morningstarrating" - The Morningstar "star" rating (mutual fund data)
"expenseratio" - The fund's expense ratio (mutual fund data)
These are just a few of the many examples that can be brought into Google Sheets from Google Finance.
A big thanks to Jann-Boje Meinecke for providing a solution to updating the Google Sheet using the IMPORTRANGE() function.
I hope you find this information helpful. If you have any questions feel free to email me at Jeff@DataPlusScience.com
