Use Google Sheets’ GoogleFinance function to display data in Geckoboard

Google Sheets has a built-in function called GOOGLEFINANCE which fetches current or historical securities information from Google Finance to a Google Sheet. This article details how this function can be used with our Spreadsheets integration to display market data on your Geckoboard dashboard. 

This function doesn't update on its own by default so we've written a script that you can adapt to ensure your data is kept up to date. Our Google's stock price example show the basics of using this function and don't use this script. If you'd like your data to update automatically, you will need to create the script once the example is completed.

• Syntax of the Function
• Geckoboard Example - Google's stock price
• Updating Your Data Automatically
• Other Resources

Syntax of the Function 

Here's the syntax of the GOOGLEFINANCE function: 

=GOOGLEFINANCE(ticker, [attribute], [start_date], [num_days|end_date], [interval])

• ticker - The ticker symbol for the security to consider.

• attribute - [ OPTIONAL - "price" by default ] - The attribute to fetch about ticker from Google Finance.

• start_date - [ OPTIONAL ] - The start date when fetching historical data.

• end_date|num_days - [ OPTIONAL ] - The end date when fetching historical data, or the number of days from start_date for which to return data.
• interval - [ OPTIONAL ] - The frequency of returned data; either "DAILY" or "WEEKLY".

Back to top

Geckoboard Example: Google's stock price

In this example, we'll use the GOOGLEFINANCE function and a Google Sheet Number Widget to display Google's current stock price on a dashboard.

Step 1: Google Sheets

1. To fetch Google's current stock price into Google Sheets, open a new Google Sheet and type the following formula into cell A2:

   =GOOGLEFINANCE("GOOG", "price")

   

   Essentially, what this formula is doing is querying Google Finance for GOOG current price (GOOG is the ticker symbol for Google Inc.). Something similar happens when you Google "Google stock price".

2. Save your Google Sheet and give it a name.

Step 2: Add a Number Widget in Geckoboard

With Google's stock price information in a Google Sheet, create a Number widget in Geckoboard to display this information on the dashboard. 

1. From your Geckoboard dashboard, click + Add Widget.
2. Choose the Spreadsheets integration.
3. Select the Google Sheets File that you just saved.
4. Select the Number widget.
5. Select the appropriate Sheet (Sheet1 in this example).
6. Enter the value of GOOG price in the Number selection (in the above example, it is A2).
7. Disable the Comparison Visualization and adjust the title and size.
8. Click Add to dashboard to add your widget to your dashboard.

Step 3: The result!

The Geckoboard dashboard is now displaying the information found in Cell A2 of the Google Sheet - Google's current stock price.

Back to top

Updating Your Data Automatically

Unfortunately, the Google Sheets GOOGLEFINANCE function doesn't automatically update. This isn't particularly helpful so we've written a script that you can adapt to update the market data every minute, even when the Google Sheet is closed. 

Step 1: Write a Script

1. Click on the Tools menu and select the option Script Editor...
2. A Google Apps Script window will appear. Click the Blank Project option.

   

3. Replace the default function with the script shown below (Important: Make sure you follow the aforementioned instructions to the letter, otherwise cells in the Sheet might not match the ones in the script and your countdown will not update automatically): 

   

4. Save your function and give it a name

Step 2: Set up a Trigger

1. From inside the Script Editor, click on the Resources menu and select the option Current project's triggers.
2. In the window that appears, click the link No triggers set up. Click here to add one now.
3. This will create a trigger for your function.
4. Select the Hour timer and leave it as Every Hour.
5. Click Save.

   

6. You'll be prompted to authorize your app by clicking Continue and then Accept. More information on Google Apps authorization can be found here.

   

7. You can now close the Script Editor.

You're done!

Now that you've written the script and set the trigger running, you'll be able to see the data on your Google Sheet refresh every hour. This will continue to refresh even when your Google Sheet is closed. 

Your Geckoboard widgets don't require any changes to reflect the addition of this script and trigger; they will refresh every 10 minutes and pull new data from your Google Sheet whenever available. 

Note: We've found that, a few times a week, a server error will occur on Google's end and the script will fail to run. By default, you'll receive an email to notify you that this has happened. Considering the script runs every hour of the day which totals 24 times, a few times a week is nothing to be concerned about.

Quotas for Google Services

When setting up your triggers, it is important to consider the Quotas and Limitations imposed by Google. For example, if you have a paid G-suite account your script can run for up to 6 hours a day, however, if you have a free gmail.com account, scripts will only run for up to 90 minutes per day. For more information please visit Google's official documentation.

Back to top

Other Resources

If you're interested in finance and economic data, you might find Quandl very useful. This website has a wide range of public (and private) data from a variety of sources available as hosted csv files i.e.  https://www.quandl.com/api/v1/datasets/GOOG/NASDAQ_GOOG.csv which can be used in combination with our CSV integration.

Back to top