Google Sheets Quote Sources

Google Sheets Spreadsheet

Use the following source parameters to update quote data from a Google Sheets spreadsheet containing values provided by the GOOGLEFINANCE security information function:

spreadsheetID range

For example, the following source parameters will update quote data using the msmquote sample Google Sheets spreadsheet as the source:

1ZKAT1tqY5dPTeEoAiRMwKPsET3xresyFZiDUtsyy-io sample!B1:Q17

You should copy the sample spreadsheet to your own Google Drive and modify as required. You must retain the column header names as in the sample spreadsheet but rows can be changed, added or deleted as necessary.

Historical Data from a Google Sheets Spreadsheet

Use the following source parameters to update historical quote data for a single security with quote data from a Google Sheets spreadsheet containing values provided by the GOOGLEFINANCE security information function:

spreadsheetID "range"

The sheet name within the range parameter must be in the format "hist symbol currency quotetype", where:

• hist is a fixed string;
• symbol matches the symbol in your Money file that you wish to update;
• currency is the currency of the quote data;
• quotetype is the quote type, currently one of EQUITY, ETF, BOND, INDEX or MUTUALFUND.

For example, the following source parameters will update historical quote data for the equity symbol TSCO.L using the msmquote sample Google Sheets spreadsheet as the source:

1ZKAT1tqY5dPTeEoAiRMwKPsET3xresyFZiDUtsyy-io "hist TSCO.L GBX EQUITY!C1:D124"

You should copy the sample spreadsheet to your own Google Drive and modify as required. You must retain the column header names as in the sample spreadsheet but rows can be changed, added or deleted as necessary.

Usage Notes

Spreadsheet ID

The spreadsheetID parameter can be found within the Google Sheets URL of the source spreadsheet as follows:

https://docs.google.com/spreadsheets/d/spreadsheetID/edit#gid=0

Using a Google Sheets Spreadsheet Quote Source for the First Time

When you run msmquote for the first time with a Google Sheets spreadsheet as a quote source you will be prompted to authorise access to your Google Sheets spreadsheets. A browser window will open automatically and you will be prompted as follows:

1. Choose the Google account that you wish msmquote to be authorised to access

2. As msmquote is currently unverified by Google you will receive this message:

   Google hasn't verified this app

   From this page click Advanced and then Go to msmquote (unsafe)

3. Finally you will receive the message

   msmquote wants access to your Google Account

   From this page click continue.