Add Real-Time Stock Prices And Metrics To Excel
We Finally Got It!
On June 5th, 2019 Microsoft released a feature to Office 365 that allows Excel users to pull real-time stock prices into their spreadsheets. This feature was never truly supported in Excel until now, even though many Excel users used to pull stock data from Yahoo! Finance until the capability ended in 2018 (thanks Verizon!).
Excel now has the ability to pull data related to stocks, bonds, currency, and even cryptocurrencies such as Bitcoin. Microsoft is working with Nasdaq and Refinitiv to pull current financial data directly into your spreadsheets under a new feature called Data Types.
What Are Linked Data Types?
Linked data types contain a connection to an online data source. They allow you to take an entity such as the country of France and automatically pull in current data related to it (ie population, leaders, GDP, etc…).
Currently, Excel has two types of Linked Data Types:
- Stocks
- Geography
There are plans to add more data types in the future. I could see some cool uses for medical info, vocabulary, and sports statistics.
What is Available Within Stocks?
Currently, these are the categories of data that can be accessed via the Stock Data Type:
- Stocks
- Bonds
- Mutual Funds
- Currencies
- Cryptocurrencies
Converting Ticker Symbols Into Stock Data
How do you get started pulling stock data into your spreadsheet? There are two methods you can use to get set up.
Start Typing Stocks
One neat feat that has come with Data Types is Excel now has the ability to realize when you are working with stock information. After you have typed three consecutive stock names or ticker symbols, Excel will most likely recognize what you are trying to do and give you a prompt to convert the cells into a Stock data type.
If you click the Convert to Stocks prompt your cells will get renamed to the official stock name stored in Microsoft’s source data and the cell will also contain Excel’s Stock Data Type symbol to the left of the stock name.
Tell Excel Your Data Is Stock Data
You also have the option to manually tell Excel your cell data should be converted into the stock data type. Simply select the cells that contain the stock names/ticker symbols and navigate to the Data tab in the Excel Ribbon. Next click the Stocks button within the Data Types group.
After clicking the Stocks button, Excel will attempt to convert as many of the selected cell’s values into stock data types. If Excel needs help, it will provide suggested options for your entered value in the right pane.
Adding Stock Metrics/Information
Now that you have Linked Stock data types set up in the spreadsheet, you can begin to add fields containing metrics and information about the specific stocks in your data set. When you select a cell that is linked to stock data, you’ll notice a small Add Field icon in the upper right-hand corner of your selected cell. If you click the icon, you’ll see a list of various fields you can add associated with that particular stock. Select one and the field will be added to the right of your current data set.
Using Formulas
If you are one to rely more on memory, there is a formula nomenclature your can write to bring in these pieces of data. Not all fields are available to all data types, so you may want to wrap your formulas with IFERROR formulas if you are comparing separate data types (ie currency wouldn’t have the employee count field available to it).
No Headings
Unfortunately, no headings will auto-populate along with the fields you add. You’ll need to manually add those yourself.
Also, note that the new field data is only added to the originally selected stock. You will need to drag down the formulas in order to get the rest of the data populating with all of your stocks.
To get around this, you can throw your data into an Excel table (ctrl + t) and the formulas will automatically be carried down by default going forward (only after you do the initial carry-down of formulas).
How To Refresh The Data?
Manually
The Stocks and Geography data types are considered linked data types. This is because they refer to an online data source. Once you convert text to a linked data type, an external data connection is established in the workbook. That way, if the data changes online, you can update it by refreshing it in Excel. To refresh the data, right-click a cell with the linked data type and click Data Type > Refresh. That will refresh the cell you selected, plus any other cells that have that same data type.
If you want to refresh all linked data types and all data connections that may be in the workbook (including queries, other data connections, and PivotTables), click Data > Refresh All or press Ctrl+Alt+F5.
Automatically Upon Open
There is also a way you can incorporate VBA code to automatically refresh the file’s stock data every time you open up your workbook. Just paste the below code within the ThisWorkbook Microsoft Excel Objects folder within the Visual Basic Editor (make sure you double-click ThisWorkbook to properly open its code page).
Private Sub Workbook_Open()
'PURPOSE: Run Data tab's Refresh All function
ThisWorkbook.RefreshAll
MsgBox "Stock Data has been refreshed!" 'Optional
End Sub
Excel May Need Help
There may be instances where you enter a company name and Excel may have multiple results for it. In this case, a blue question mark will appear next to your entry. You will need to click it to clarify which particular stock you are wanting to bring into your spreadsheet.
After clicking the blue question mark icon, you should see the Data Selector pane appear on the right side of your window. Simply go through the list of suggestions and select the ticker symbol you were intending to track.
The selected ticker information should then automatically populate within your table.
Stock Cards
Linked Data types also have a neat feature called Cards (I know, creative name, right?!). Cards will appear if you click the bank icon to the left of the stock name. Cards can be a great way to answer ad-hoc questions on your mind without having to bring that data into your spreadsheet.
More Stock-Related Links
- Pulling Historical Stock Data in Excel
- How To Pull Cryptocurrency Prices In Excel
- VBA Guide To Refresh Data Connections
What’s Next For Stocks?
Microsoft has taken a giant step in the right direction by providing a much sought-after feature that actually works extremely well! Next, Microsoft is working on integrating the ability to pull in historical information based on date ranges into our spreadsheets. As you can imagine, this is the capability everyone is really wanting because of the potential to build full-scale models and in-depth analyses tailored to your specific investing strategies. Hopefully, this feature gets released soon!
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.