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).
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’)
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 inReal-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)’.
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.
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.
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.
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.
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.
Make the first row of data into the column headings.
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.
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.
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.
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:
Frankfurt:
Hong Kong: number codes are used.
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.
