8/31/2016
Google Finance Information in Tableau 10 utilizing Google Sheets Connection
NOTE: Publish up to date 9/19/2017 because of an answer offered by Jann-Boje Meinecke utilizing the IMPORTRANGE() perform. The IMPORTHTML() perform can be utilized as a substitute of the GOOGLEFINANCE() perform and Adam Crahen has glorious directions on learn how to set that up posted here
Just a few weeks in the past I posted directions on Connecting Google Sheets with Tableau 10. This system connects information from Google Sheets to Tableau and updates it each day. Within the earlier put up I imported information into Googe Sheets utilizing the IMPORTHTML perform and returned information from a desk. On this instance, I’m going to make use of a built-in perform in Google Sheets for historic monetary information which comes from Google Finance. The construct directions are very comparable.
Step 1 – Construct the Google Sheet:
To begin, create a Google Sheet with the monetary information
1.) Create new Google Sheet, instance « Inventory Information »
2.) Put a column title in cell A1 for « Image » and enter the inventory image in cell A2. On this instance, I used DATA which is the ticker image for Tableau Software program.
3.) Create a GOOGLEFINANCE system in cell B1. The syntax is:
=GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])
These formulation will learn the inventory image you entered in cell A2 and return the historic information for the inventory image from Google Finance. The system in B1 is:
=if(right now()=right now(),GOOGLEFINANCE(A2, »all »,TODAY()-365,TODAY()), » »)
Observe: To make the most of the auto refresh in Google Sheets on the GOOGLEFINANCE perform I added an IF assertion utilizing right now() = right now() which ought to hearth off the system in accordance with the Google Sheets setting (#6 beneath).
4.) If it’s worthwhile to use the image in your viz then create a system for the image in cell A3 and duplicate and previous the inventory image in column A down to finish of the information (optionally available). The system I utilized in A3 is:
=if(ISBLANK(B3), » », « DATA »)
This populates the image for any cell that’s returned from Google Finance in Column B. Copy and paste down by way of Row 365 to insure that the image is populated on the sheet for something with information.
Observe: Inventory information is just returned for weekdays, so utilizing the system above does not really return 365 rows.
5.) Click on Share within the high right-hand nook, set the setting so this doc might be seen by others and duplicate the URL.
6.) Click on « File » -> « Spreadsheet setting » and set the « recalculate » to « On change and every single day ». This can hold the information refreshed, studying straight from Google Finance.
You now have a Google Sheet with the historic value and quantity for Tableau’s inventory. My completed sheet is here.
Step 2 – Construct a Second Google Sheet to Learn from the First (up to date 9/19/2017
NOTE – This step was added to unravel downside importing into Tableau)
1.) Create new Google Sheet with a special title than the primary one, instance « Inventory Information for Import »
2.) Use the IMPORTRANGE() perform to learn the information from the Google Sheet you created in step 1.
The system in cell A1 makes use of the hyperlink copied above in Step 1 ought to look one thing like this:
=IMPORTRANGE(« https://docs.google.com/spreadsheets/d/1hyOk9nbnPRU1TepMKpPctlDxf1krTvZbb8UI7gRgYEc/edit », « Sheet1!A1:G489 »)
3) Click on Share within the high right-hand nook, set the setting so this doc might be seen by others and duplicate the URL.
4.) Click on « File » -> « Spreadsheet setting » and set the « recalculate » to « On change and every single day ». This can hold the information refreshed, studying straight from Google Finance.
Step 4 – Connect with Tableau:
1.) That is tremendous easy. Simply open Tableau and choose a brand new database supply as Google Sheets.
2.) Enter your credentials and choose the second Google Sheet you created in Step 2 or enter the URL that you just copied in Step 2.
3.) The information will now load into Tableau.
4.) Construct your viz with the information from the Google Sheet.
Step 3 – Publish to Tableau Public and Preserve Information in Sync:
1.) Once you publish to Tableau Public, ensure to test the field « Preserve my information in sync with Google Sheets and embed my Google credentials ». This can hold the information up to date, refreshing each day.
2.) As soon as on Tableau Public you’ll be able to request an replace of the information by clicking « Request Replace ».
Under is a visualization placing the items collectively (link to Tableau Public here). This exhibits Tableau’s inventory value on the highest line chart and the quantity of the each day buying and selling on the underside chart, a quite common view for visualizing shares.
That is one other instance of seamless integration of Google Sheets in Tableau 10. Here is a list of the entire attributes availabe within the GOOGLEFINANCE perform.
Just a few examples of attributes:
« value » – Realtime value quote, delayed by as much as 20 minutes (realtime information)
« marketcap » – The market capitalization of the inventory.
« pe » – The worth/earnings ratio (realtime information)
« eps » – The earnings per share (realtime information)
« high52 » – The 52-week excessive value (realtime information)
« low52 » – The 52-week low value (realtime information)
« open » – The opening value for the desired date(s) (historic information)
« shut » – The closing value for the desired date(s) (historic information)
« low » – The low value for the desired date(s) (historic information)
« quantity » – The amount for the desired date(s) (historic information)
« all » – The amount for the desired date(s) (historic information)
« closeyest » – The day prior to this’s closing value (mutual fund information)
« return52 » – Fifty-two-week (annual) complete return
« return260 » – 260-week (5-year) complete return (mutual fund information)
« incomedividend » – The quantity of the newest money distribution (mutual fund information)
« morningstarrating » – The Morningstar « star » ranking (mutual fund information)
« expenseratio » – The fund’s expense ratio (mutual fund information)
These are just some of the numerous examples that may be introduced into Google Sheets from Google Finance.
A giant because of Jann-Boje Meinecke for offering an answer to updating the Google Sheet utilizing the IMPORTRANGE() perform.
I hope you discover this data useful. When you’ve got any questions be at liberty to electronic mail me at Jeff@DataPlusScience.com
Jeffrey A. Shaffer
Observe on Twitter @HighVizAbility
