Finding the right stock or index among the tens of thousands available in Excel’s Stock data type isn’t always easy. Here’s some stock search tips that might help with some advice about a changing situation.
Stock data type is available in Excel 365 for Windows and coming to Excel 365 for Mac (currently only Insiders). The basics are easy, type in a stock name, then click Data | Stocks.
Add some extra fields to see a lot and the latest information about that stock. That includes the latest stock price.
Real-Time Excel – get live stock prices, currency rates and more our ebook has details on the Excel 365 Stock data types including example worksheets ready to use.
Traps for the unwary …
If only it were that easy. The Excel Stock Data feed is enormous, covering over 60 different stock exchanges from around 50 countries.
Office-Watch.com has warned about stock searches in Excel before Excel 365 Stock data type, watch the ticker code!
Looking for the right stock or index, there are some things to consider. Details that you don’t need to worry about when looking for a company on a single stock exchange.
- Some companies have the same or similar names.
- Very different companies can have the same ticker code, but listed on different exchanges. Here’s three companies all with the ticker code ‘AC’ but listed on different stock exchanges (Toronto, Philippines and New York)
- The same company can be listed on many different stock exchanges. Apple and Microsoft are listed in many exchanges around the planet and Excel’s Stock data type can show them all.
- A company might have different stock types listed in addition to the ‘main’ stock that most are familiar with. It’s not enough to find the right company and exchange, also the correct stock (financial instrument).
Double check
Always double check the result that Excel gives you from a Stock data search.
Make sure the company name, ticker code and exchange code are all correct.
Verify that the price quoted makes sense. Check it against another source (web site, newspaper etc) to see that the price is in the same range as Excel is showing.
Once you’ve verified that Excel is showing the stock you want, you’re good to go. Excel won’t change the stock details shown once you’ve selected it.
Reliable Stock search tricks
The most reliable way to search for a stock is using a combination of the Exchange Code and Ticker code, separated by a colon.
EXCHANGE:TICKER should take you straight to the stock you want.
Alternatively, try COUNTRY_CODE:TICKER for example US:AAPL for Apple’s stock listed on a USA exchange. Use two-letter country codes listed here.
Here’s some examples, the last two ‘typing’ columns show the exchange/country plus ticker combinations that took us to that stock.
A work in progress
That’s the situation at the end of January 2019 but it will improve in the months ahead for several reasons.
Microsoft is changing the stock data supplier to what will, hopefully, be a more reliable and consistent data feed.. That will happen over the next few months, starting with Office Insiders.
Linked Data Searches don’t just look at the cell you type, it takes into account the cells surrounding the main cell. Exactly how that contextual search works is being refined over time.
Change of Exchange Code
The change of data suppliers will mean a change in Exchange Codes from the three-letter codes now in use to the four-letter international standard codes. For example the NASDAQ will be XNAS instead of NAS or the London Exchange will change from LON to XLON.
Search changes
Unseen by us mere mortals, there’s a lot of work at Microsoft refining the search system for both Stock and Geography data types. They are looking at how people are using Stock data and trying to make it better and more reliable.
One likely change is more flexibility in what you can type. TICKER:EXCHANGE should work as well as EXCHANGE:TICKER does now.
As well as the colon, it seems likely that a comma, hyphen/dash, period/fullstop or even just a space will also work.
Watch this space …
If you have problems with Stock or Geography data in Excel 365, let Microsoft know.
For the moment, stick with EXCHANGE:TICKER which should work consistently while other changes are happening.
Real-Time Excel – get live stock prices, currency rates and more our ebook has details on the Excel 365 Stock data types including example worksheets ready to use.
