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 end in 2018 (thanks Verizon!).

Excel now has the ability to pull data related to stocks, bonds, currency, and even crypto-currency 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:

1. Stocks

2. 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 setup.

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 click 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 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 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 it’s code page).

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 or 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 adhoc questions on your mind without having to bring that data into your spreadsheet.

What’s Next For Stocks?

Microsoft has taken a giant step in the right direction with 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 analysis tailored to your specific investing strategies. Hopefully, this feature gets released soon!

Credits: Excel Spreedsheet Tips