How to get free stock market data in Excel: an illustrated guide
Until now, getting stock market quotes in Excel has required some combination of technical expertise and specialist financial software. Not any more!
I was both surprised and happy to see that the latest iteration of Microsoft Excel includes convenient stock market quotations and company data as standard. Now anybody can get this information with complete ease. We can only hope that it lasts.
Let me walk you through the process. I’m still amazed at how simple it is!
Step 1: Make sure you have the latest version of Excel
This is the bit where you probably have to pay something. In the UK, Office 365 Business costs £7.90/month + VAT and includes the latest version of Excel.
The non-business version of the product – Home – should also work. The Home version costs £80/year.
According to Microsoft, as of November 27 2018: “Data types have been released to all Windows Office 365 subscribers, except in a few geopolitical locations (coming soon).” If you’re in any doubt as to whether the service is available in your location, you should contact Microsoft!
Step 2: Type in some company names in a column
You can type in the company names or the ticker codes, like I’ve done below:
Step 3: Select all the names/ticker codes using your mouse or keyboard
I told you this was going to be simple:
Step 4: Tell Excel that these are stock market companies
You do this by clicking on “Stocks” in the “Data” section at the top of the screen:
Step 5: Get the Stock Exchange symbol and official company names
Ta-da! You know it works when you see the Greek architecture and your company names/codes get converted into the official PLC company names.
If Excel doesn’t recognise a name, you should be given a list of options to choose from. If you’re having difficulty, try again with a more precise version of the company name as your input.
Step 6: Time to get some stock exchange data
Select the PLC company names and look for the “Insert data” button which should show up at the top-right of the column:
Step 6: Choose your first variable
A list of variables should show up. If you were wondering where the company’s headquarters are, or the name of the CEO, you’re in luck. Or if you’re like me, you’ll be interested to know what the latest share price is. So scroll down the list and click on “Price”.
The column to the right of the company names should fill up:
Step 7: Repeat
Suppose you want the market cap. Select the company names again, and (you guessed it) click on “Market Cap” from the list.
Every time you choose a new variable, it fills up the column to the right of your existing table.
Step 7: Continue & Tidy Up
Keep going until you have all the variables you need, and then tidy up the table. For example, my table might look like this:
Step 7: Stay Refreshed
The data is provided by Morningstar and is on a 15-minute delay for the London Stock Exchange, New York Stock Exchange and NASDAQ.
Keep your table up to date by hitting the Refresh button (pressing Ctrl+Alt+F5 will also do the trick).
You now have all the essential knowledge you need to start experimenting with this new toy. How are you going to use it?