Excel: stock prices from Google Finance

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

Here’s another method of getting stock prices into Excel.  It’s part of a series: introduction and using MSN Money in Excel.

This time we’ll use the improved Query system in recent versions of Excel combined with data from Google Finance.

It’s a sad indictment of Microsoft Office that the best place to get stock data is Google.

Google’s version of Excel is Sheets and that has a nifty function that grabs stock prices right into the worksheet.  Once you get the data into a Google Sheet, it can be automatically exported and grabbed by Excel.

It’s an indirect method but effective.  Google’s GoogleFinance() function returns a lot of information about many global stocks and indexes.    18 different ‘attributes’ for each stock or index – too many to show in a single screen image (they’re listed later in this article).

excel stock prices from google finance 10797 - Excel: stock prices from Google Finance

To make this work, create a new Google Sheets from the online service (not the downloadable software).  Go to Google Drive and make a new sheet.

Add to the sheet codes you want to look up, then a column with calls to GoogleFinance() to lookup those codes.  For simplicity we’ll just lookup the price. We’ve added another column with the vital currency detail as well  eg GoogleFinance(A3,’currency’)

excel stock prices from google finance 10799 - Excel: stock prices from Google Finance

Sheets works similarly to Excel for these operations. There’s an autocomplete in the function entry line and you can copy then paste to fill cells too.

Extra:  you can view only the Sheet that Peter Deegan made for these examples at https://docs.google.com/spreadsheets/d/1ZPfVybR-8HNlo4qXjP14B5guXQiaFuw9iz3xOGgxdgY/edit?usp=sharing  It’s a READ ONLY worksheet with editing not available, for obvious reasons. The usual 21st century disclaimers, use at your own risk etc.

Get full details on stock price integration with Excel in Real-Time Excel – get live stock prices, currency rates and more – less than US$12 or even a measly US$7 for Office-Watch.com subscribers (which is free).

Each column has a standard heading with the GoogleFinance() attribute in the 2nd row.

Once you have a simple sheet to start with, the next step is to publish it in a form that Excel can understand.  Go to File | Publish to the Web

Choose the worksheet/tab you want to publish and select ‘Comma-separated values (.csv)’.

excel stock prices from google finance 10800 - Excel: stock prices from Google Finance

Make sure the ‘Automatically republish when changes are made’ option is on.

Copy the supplied link so you can use it in Excel.  The link will download a .csv file with the data from the worksheet cells.

Switch to Excel (in this case Excel 2016 for Windows).  Go to Data | New Query | From File | From CSV.

excel stock prices from google finance 10802 - Excel: stock prices from Google Finance

You’ll be presented with a standard File Open dialog but you don’t have to select a saved .csv file.  Paste in the link from Google Sheets then click Open.

excel stock prices from google finance 10803 - Excel: stock prices from Google Finance

After a pause, Excel will show you what it’s downloaded and how it suggests dealing with the data. It should do an accurate job dealing with the incoming data.

excel stock prices from google finance 10806 - Excel: stock prices from Google Finance

Click Load and Excel will make a new worksheet for you.

Currency formatting

In the above example, you’ll see that the Sheets column was formatted with the correct symbols for each currency (Sterling and Euro).

Unfortunately, that doesn’t translate correctly.  See the Sheets column on left and the Excel version on the right.

excel stock prices from google finance 10807 - Excel: stock prices from Google Finance

The solution is to change Sheets to Number format, with no currency symbols.  Do any currency formatting in Excel.

Query Editor

The setup isn’t finished, right click on the query and choose Edit to fix a few things.

excel stock prices from google finance 10808 - Excel: stock prices from Google Finance

Make the first row of data into the column headings.

excel stock prices from google finance 10810 - Excel: stock prices from Google Finance

Change the sort order to put Symbols in alphabetical order.  This makes them available for VLOOKUP() searches and a very welcome (ie overdue) inclusion in Excel.

excel stock prices from google finance 10812 - Excel: stock prices from Google Finance

Finally, go to Query | Properties and rename the query to something more helpful than the link text.

Click Close and Load to leave the Query Editor.

Now you have a ‘live’ data worksheet that you can use to lookup prices etc to insert into your own calculations.

excel stock prices from google finance 10813 - Excel: stock prices from Google Finance

Finding Stock Codes

Here’s how to find stock or index codes to use with the GoogleFinance() function.

Search Google Finance for companies across many different markets.  The code will be in brackets after the company name.

excel stock prices from google finance 10815 - Excel: stock prices from Google Finance

For US companies, the market prefix (eg NYSE or NASDAQ) isn’t necessary.  NYSE:WMT and WMT both work as do NASDAQ:MSFT and MSFT.  But you might like to add it anyway, to distinguish the same company being listed on multiple exchanges.

International exchanges are also well represented.  The exchange prefix is required.

London:

excel stock prices from google finance 10817 - Excel: stock prices from Google Finance

Frankfurt:

excel stock prices from google finance 10818 - Excel: stock prices from Google Finance

Hong Kong: number codes are used.

excel stock prices from google finance 10821 - Excel: stock prices from Google Finance

GoogleFinance()

If you just want the latest price, simply add the stock code eg  GoogleFinance(“MSFT”) or GoogleFinance() function supports the following attributes to use with stock quotes.  Use these in the second parameter of the function eg GoogleFinance(“MSFT”,”priceopen”):

  • “price” – Realtime price quote, delayed by up to 20 minutes.
  • “priceopen” – The price as of market open.
  • “high” – The current day’s high price.
  • “low” – The current day’s low price.
  • “volume” – The current day’s trading volume.
  • “marketcap” – The market capitalization of the stock.
  • “tradetime” – The time of the last trade.
  • “datadelay” – How far delayed the realtime data is.
  • “volumeavg” – The average daily trading volume.
  • “pe” – The price/earnings ratio.
  • “eps” – The earnings per share.
  • “high52” – The 52-week high price.
  • “low52” – The 52-week low price.
  • “change” – The price change since the previous trading day’s close.
  • “beta” – The beta value.
  • “changepct” – The percentage change in price since the previous trading day’s close.
  • “closeyest” – The previous day’s closing price.
  • “shares” – The number of outstanding shares.
  • “currency” – The currency in which the security is priced.

Google Finance should have a ‘Long Name’ attribute so you can confirm that the stock code is returning the data you intend.  That’s especially true for codes such as the Hong Kong bourse which are numbers, not letters.

The function also supports Mutual Funds and historical data check out the GoogleFinance() help page for full info.

subs profile e1563205311409 - Excel: stock prices from Google Finance
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address