FREE Stock Lookup in Excel – LIVE Data Updates
In this article, I will go over the following.
- Features of the Stock Lookup template
- Introduction to the Stocks data type
- 2 types of formulas to retrieve this data and refresh.
- Some issues or gaps I experienced
Stock Lookup Template features
Type a ticker symbol to see relevant info. Example: I type in MSFT
The template shows the information in different sections
- Latest Price and Change in price
- Details of the exchange and the Currency
- About the Company
- Latest day Price range
- Market Cap and P/E
- Price history in the 52 weeks
- Shares outstanding and trade volume
- Time that the data represents
- I have entered an offset of -7 hours from UTC to arrive at pacific time where I live.
- Please use this link (savvytime.com) if you need to know how many hours ahead or behind. If you are ahead, enter positive number. If behind enter negative number.
You can change the Ticker symbol and immediately view the information for a stock.
Now, let’s learn about how this template was built.
The foundation of this is the Stocks Data type.
About Stocks Data type in Excel
With the ‘Stocks’ Data Type, Excel has introduced a new way to bring additional data into Excel via a service.
Let’s type a symbol in a new sheet. You can type any symbol in any cell and then convert to stock data type.
If the symbol is not unique, Excel will offer the choices. For example, MSFT. Select accordingly.
As an alternative, you can type the exchange in front of the ticker with a colon in between, like XNAS:MSFT
If Excel is picking the wrong value automatically, you can Change Data type to modify the associated stock.
After converting to data type, Right click and choose ‘Show Data Type Card’.
Now this is all the information that is available. What we are doing in our template is to make this more customizable in layout and presentation.
In addition to individual Stocks, you can get data about Indices and Currency exchange data as well.
Here is the link to the article from Microsoft which shows which exchanges are available and the delay in data refresh for each.
To see all the cells where we have used formulas, in the Editing section of the Home ribbon, Find —-> Formulas
There are two different methods we have shown here on how to pull all the rich stock data into Excel.
- Dot formulas
- To pull the latest price, we have used this dot formula.
- I_SYM is the name of the cell where we receive the input ticker symbol.
- You can type a dot and then choose which field you need to extract. Very straight forward.
- FieldValue function
- The second method which I have used a lot in this template is the FieldValue function.
- We provide two inputs to this function. Value – which is our ticker symbol and the field name is any of the field names available with the data type. In this case, I am pointing to this ‘Exchange Abbreviation’ text.
- I prefer this because this is dynamic based on a cell reference. For example, I can type in Employees instead of Year incorporated, to have employees show up in the cell above.
- To accommodate any missing data, we wrap it with an IFERROR function.
To see all the fields available, you can do the dot formula or see the ‘Show Data Type Card’ as explained earlier.
This field list will likely change over time and also may differ by country. I am not sure. Please share your experience on if you see more fields or if you don’t see these fields I am seeing. Please mention the country and exchange you use, in the comments below.
How to refresh?
To refresh the price and other information, just use the regular Excel refresh in the Data ribbon.
- There is no complete list of symbols that I could find. If anyone does, please let me know.
- Not all the stocks or indices seem to be available even within the exchanges Microsoft says is available. Index data is available for some not for all. I can find S&P500 Index but not Russell 2000 for example.
- Search is not very consistent and we have to use codes in a specific way to make it recognize. For example, searching for NASDAQ 100 didn’t work.
- However, if we knew the NDX code, we would be able to find the Index as shown below.
- Dividend data is not available for stocks
- Commodities/Futures do not seem to be available at this time. Hopefully they will be enhancing it soon in the future.
Microsoft has introduced a new feature in Excel called Data types. One of the data types is ‘Stocks’. That’s what we will be using for these templates.
This feature is available in Microsoft 365 plans and also in the free Excel for the Web. I will provide links to both.
Please share your experience. I expect this to be different with different exchanges and countries. Is it working for you? Is it working differently? I would love to hear from you.
I believe there may be an error in the 1-Stock-Loockup-Template.xlsx file in the cell that shows the daily price increase/decrease concatenated with the price daily percentage increase/decrease.
The daily percentage decrease is not showing correctly for some reason. I’ve right-clicked to show the Data Type Card for the stock and the % decrease for the stock is different between the spreadsheet and the Data Type Card.
Thanks you so much for making these spreadsheets freely available, much appreciated, along with the meticulous instructions.
Thank you for showing interest in our template and sharing your valuable feedback.
The daily price & % of increase/decrease is directly taken from Microsoft stock database for the entered ticker symbol. Please share your sheet to firstname.lastname@example.org along with the screenshot of correct value and incorrect value shown at your end to check further.
The graph is not updating with the prices for the selected symbol – it’s always showing the MSFT graph, no matter the symbol….
Thank you for showing interest in our template.
I just tested the template and it is updating in our end. Try right-clicking the ticker symbol and select Data-Type -> Change. If the data type option is not available on right-clicking the ticker symbol, it means that you are using an excel version that does not support Excel stop data type. Hence try uploading the sheet to Microsoft OneDrive and use Excel online to use the template.
How did you make the blue bar atomically update when a ticker is entered? On my sheet, I always have to manually select from the Data Selector window to have the ticker update and pull data. Thank you for your help.
Thank you for showing interest in our template.
Try entering a ticker symbol without deleting the existing stock data type or you can also right-clic the existing ticker symbol and select Data Type – change to change the ticker symbol.