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.

Note

Widgets powered by spreadsheets using the GoogleFinance function refresh approx. every 15 minutes. If this isn't fast enough for your needs, you can run a script and set your own refresh interval.

Turn spreadsheet data into shareable dashboards

Syntax of the GoogleFinance 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".

GoogleFinance function in action

Example: How to display Google's stock price on a dashboard

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.

  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")Google_Finance_sheet

    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.
  3. With Google's stock price information in a Google Sheet, switch to your Geckoboard dashboard.
  4. Click Add widget.
  5. Choose the Spreadsheets integration.
  6. Select the Google Sheets file that you just saved.
  7. Select the Number widget.
  8. Select the appropriate Sheet (Sheet1 in this example).
  9. Enter the value of GOOG price in the Number selection (in the above example, it's A2).
  10. Disable the Comparison Visualization and adjust the title.
  11. Click Add to dashboard to add your widget to your dashboard.Add_number_widget
  12. And that's it! Your Geckoboard dashboard is now displaying the information found in Cell A2 of the Google Sheet – Google's current stock price.Google_Finance_result

Optional: Speed up refresh periods for GOOGLEFINANCE

Widgets powered by spreadsheets using the GOOGLEFINANCE function refresh approx. every 15 minutes. If this isn't fast enough for your needs – and your G Suite account allows it – you can write a script that can further reduce the refresh times.

Please open a new Google Sheet to work through these next steps.

  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.Setup_script_blank_project
  3. Replace the default function with the following script:

    Important note

    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

    function getData() {
    SpreadsheetApp.getActiveSheet().getRange('A2').setValue('=GOOGLEFINANCE("GOOG", "price")');
    }
  4. Save your function and give it a name.
  5. From inside the Script Editor, click on the Edit menu and select the option Current project's triggers.
  6. Click on the project's trigger's menu.trigger_for_daily_update.png
  7. Click the + Add Trigger button in the bottom right corner of the page. add_trigger.png
  8. Set a trigger for the myFunction function, that runs from Head on a Time-driven source that's triggered on a Minute timer.trigger settings in Google Sheets
  9. Once you set the trigger (and so that you don't have to wait 1 hour to get data), run the script for the first time.run_script.png

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 faster than the default 15 minutes. This will continue to refresh even when your Google Sheet is closed.

Potential Google server errors

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 minute of the day which totals 1440 times, a few times a week is nothing to be concerned about.Google_server_error.png

Tip

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/v3/datasets/WIKI/GOOGL.csv which can be used in combination with our CSV integration.

Was this article helpful?

Awesome! 👍 Thanks so much for your feedback!

Sorry about that! Start a conversation now.

Ready to create your own dashboard?

Get started for free

Still have questions? Get in touch.