We finally did it!
On June 5, 2019, Microsoft released a feature for Office 365 that allows Excel users to insert real-time stock quotes into their spreadsheets. This feature was never really supported in Excel, although many Excel users use Yahoo! Financing until end of performance in 2018 (thanksVerizon!).
Excel now has the ability to pull data from stocks, bonds, currencies, and even cryptocurrencies like Bitcoin. Microsoft is partnering with Nasdaq and Refinitiv to provide up-to-date financial data in a new feature called "type of data.
What are linked data types?
Linked data types contain a connection to an online data source. They allow you to take an entity like the country of France and automatically pull up-to-date data about it (ie population, executives, GDP, etc.).
Excel currently has two types of linked data types:
Behavior
Geography
There are plans to add more data types in the future. I could see some interesting uses for medical information, vocabulary, and sports statistics.
What is available in stock?
Currently, these are the categories of data that can be accessed through the inventory data type:
Behavior
Link
investment funds
coins
CRYPTOCURRENCIES
Convert stock symbols to stock data
How do you start to include inventory data in your spreadsheet? There are two methods you can use to set it up.
Start typing actions
A decent performance that cametype of dataExcel now has the ability to recognize when you are working with inventory information. After you enter three consecutive stock names or symbols, Excel will probably recognize what you're trying to do and prompt you to convert the cells to a stock data type.
When you clickconvert into sharesYour cells will be immediately renamed to the official stock name stored in Microsoft source data, and the cell also contains Excel filesStock data type iconto the left of the action name.
Tell Excel that your data is inventory data
You also have the option of manually telling Excel to convert the data in your cell to the inventory data type. Simply select the cells containing the stock/ticker symbol names and navigate todata guideon the Excel ribbon. Then clickshare buttoninsidedata type group.
After clickingBehaviorclick, Excel will try to convert as many values in the selected cell to value data types as possible. If Excel needs help, it offers suggested options for the value entered in the right pane.
Add metrics/stock information
now that you haveData Types for Linked ActionsSet up on the table, you can start adding fields that contain metrics and information about specific actions in your data set. If you select a cell linked to stock data, you will notice a smalladd fieldicon in the upper right corner of the selected cell. When you click on the icon, you'll see a list of different fields you can add that are tied to that specific stock. Select one and the field will be added to the right of your current record.
using formulas
If you rely more on memory, there is a naming formula you can write to bring this data back. Not all fields are available for all data types, so you may want to group your formulas with IFERROR formulas when comparing separate data types (ie, for currencies, the Number of Employees field would not be available).
no titles
Unfortunately, no header is auto-completed with the fields you add. You must manually add them yourself.
Also note that the new field data is only added to the originally selected inventory. You must drag the formulas to fill in the rest of the data with all your actions.
To avoid this, you can put your data in an Excel spreadsheet (ctrl+t) and the formulas will be pasted automatically by default (only after you have downloaded the first few formulas).
How do I update the data?
a cunning
The Stocks and Geography data types are consideredconnectedtype of data. This is because they refer to an online data source. After the text is converted 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 update the data, right-click a cell with the linked data type and clicktype of data>Update. This updates the selected cell, as well as all other cells with the same data type.
If you want to update all related data types and data connections that may be in the workbook (including queries, other data connections, and pivot tables), clickData>update allor pressCtrl+Alt+F5.
automatically when open
There is also a way to include VBA code to automatically update the file's inventory data each time you open your workbook. Just paste the following code inthese workbooksMicrosoft Excel Objects folder in the Visual Basic Editor (make sure to double-click ThisWorkbook to properly open its code page).
Private SubWorkbook_Open()
PURPOSE: Run the Update All function from the Data tab
DieseArbeitsmappe.RefreshAll
MsgBox "Inventory data has been updated!"'Optional
movie Sub
There may be cases where you enter a company name and Excel may have multiple results for it. In that case, a blue question mark will appear next to your entry. You need to click on it to clarify which specific action you want to include in your spreadsheet.
After clicking the blue question mark icon, the data selection panel should appear on the right side of the window. Just scroll through the list of suggestions and select the stock symbol you want to track.
The information from the selected teletype must be automatically entered into your calculation sheet.
common cards
Bound data types also have a nice feature calledcards(I know, creative name right?!). Maps are displayed by clicking onBank-Symbolto the left of the action name. Maps can be a great way to answer ad hoc questions without having to bring that data into your spreadsheet.
More Action Related Links
Get historical stock data in Excel
How to extract cryptocurrency prices in Excel
VBA Guide to Update Data Connections
What's next for stocks?
Microsoft has taken a big step in the right direction by providing a highly requested feature that actually works very well. Next, Microsoft is working on adding the ability to pull historical information into our spreadsheets based on date ranges. As you can imagine, this is the skill that everyone really wants, as it gives you the potential to create comprehensive models and in-depth analysis tailored to your specific investment strategies. I hope this feature will be released soon!
About the Author
Hey! My name is Chris and I run TheSpreadsheetGuru website in my spare time. By day, I'm actually a finance professional who relies heavily on Microsoft Excel in the corporate world. I love sharing the things I'm learning in the "real world" with everyone here on this site, so you too can become a spreadsheet guru in your business.
My years in the corporate world have allowed me to seize opportunities to improve working with Excel and create countlessExcel add-ons, from inserting scale symbols to automatically copy/paste from Excel to PowerPoint. If you want to stay up to date with the latest Excel news and get the best Excel tips I've learned over the years straight to your email, you canSign up for my free newsletter. I hope I was able to offer you something of added value today and hope to see you around again soon!
-cris
Founder, TheSpreadsheetGuru.com