If you have Microsoft 365 (formerly Office 365) you can use Excel’s new data types to create Excel formulas that reference the most recent information from the web. For new data types we have Stocks, Currency and Geography. I am using a version of Microsoft 365 and I have an Internet connection to pull the data in.

Stocks

You can get stock, geographic and currency data in Excel. It’s as easy as typing text into a cell, and converting it to the Stocks data type, or the Geography data type, by selecting the Data tab and clicking on Stocks or Geography. These two data types are considered linked data types because they have a connection to an online data source. That connection allows you to bring back rich, interesting information that you can work with and refresh. If you are interested in a past information on a stock, have a look at this post called Excel’s Stockhistory Function that discusses the STOCKHISTORY function.

After clicking on Stocks with the cursor in the cell with “MSFT” in it, you might see the question mark in front of your text and the following selector on the right side.

I will select the first one, which is the Nasdaq stock market. Now I get the following where I can extract information about this stock.

If I hover over the little institution icon at the beginning of the cell and I click I get the card, as shown below.

Here is a YouTube video called Get Latest Stock Data in Excel & Create Your Own Stock Portfolio. The video is by Leila Gharani. There is a lot more information in the video than is not in this post.

You can place data into cells. If you click on the Insert Data icon just off to the right of the cell, you get the pop up list of data types that you can click on to place into cells to the right of the MSFT cell. Notice that they use a formula.

Do you want to create a dashboard of your portfolio of stocks? Check out the original Dashboard by Mynda Treacy here: Excel Stock Portfolio Dashboard – FREE File Download.

An advanced example of using the custom data type can be found in the course Excel Power Query Beginner to Advanced (including M) at Udemy.com on video 78.