How to get free stock market data in Excel: an illustrated guide

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?

2+
CATEGORIES
Share This

COMMENTS

Wordpress (3)
  • comment-avatar

    That’s really great. Except I have Microsoft Excel Home 2013. I tried going to “file” menu, then “account” to download latest version, but still don’t get the Greek architecture icon. But very good to know. Might be worth paying MS £80 a year.

    • comment-avatar

      Bruce,

      Are you a Stockpodeia subscriber? If so but you also have shares in an excel spreadsheet then it’s “fairly” easy to update your excel file share prices from a Stockpodia Portfolio. Not quite as simple as Graham’s “new” excel method but doable.

  • comment-avatar

    Hhh. Thanks Graham. Nice to know it’s so simple but, like Bruce, I have an older version of Excel and accept it won’t work with that.

    But, how is this any better than having a “Portfolio” or “Portfolios” of interesting shares in Stockopedia, assuming that you’re already a subscriber?

    1+
  • Your Cart