Link to home
Start Free TrialLog in
Avatar of reallygorgo

asked on

Stock price info into Excel spreadsheet

I would like to look up stock price information (NYSE, TSX) in Excel, but I understand that Yahoo no longer offers this service. Is there a way of doing this?
Avatar of byundt
Flag of United States of America image

Microsoft offers time delayed stock prices for thousands of tickers on exchanges around the world. You need Excel 2016 on an Office 365 subscription to get the feature--and at the moment, you also need to be an Office Insider.

If you have the feature, you will see a Data Types group in the middle of the Data menu. There are two Data Types available: Stocks and Geography.
User generated image
Start by typing a company name, ticker symbol or place name in a cell. Then use the Data...Stocks or Data...Geography menu item to convert that cell into a Stocks or Geography data type. Note that the value in the cell changes, and an icon is displayed.

The Stock data type allows you to get values for 31 properties, with more coming in the future. Some of the properties, such as stock price, are live (though delayed by 15 or 20 minutes). Other properties, such as CEO or Industry, are more static.

Data are powered by Bing, and are available for stocks listed on principal exchanges around the world. Right now, Bing gets its data from Morningstar Inc.

Rightclick a cell and choose Data Type...Show Card from the resulting dialog. Make note of the property names. To get the value of one, you may build a formula like these:
=B$1.[Ticker symbol]
=FIELDVALUE(B$1,"Change (%)")
=FIELDVALUE(B$1,[Change (%)]
You don't need the square braces in the second formula because Price is a single word.

You will be wanting to refresh the time sensitive data. To do so, rightclick any cell with Stock data type and choose Data Type...Refresh. The VBA statement equivalent to that is ThisWorkbook.RefreshAll

At present you need to be an Office Insider to obtain the Stocks Data Types feature.

First of all, you must be running a subscription copy of Office 2016/Office 365. If you have a perpetual license version, no changes are possible, and you are stuck with the feature set that existed when that version was first released. But if you have a subscription copy, you may read about the different Channels and how to change them at 

Office 365 Home, Personal, and University subscribers
Changing channels is simple. To get Insider or Monthly Channel (Targeted) updates for Office for Windows desktop, you have two options:
In any Office 2016 app, click File...Account...Office Insider. Select the Get early access to new releases of Office box, specify an update level, and then click OK.

Alternatively, visit the Additional Install Options of My Account. Sign in with your Microsoft Account, if needed. In the Version menu, click the type of Insider build you want to install, and then click Install. If you already have Office 2016 installed on your Windows desktop, you do not need to uninstall it first. You’ll be updated to the Office Insider build. If you don’t see an Insider option on the Version menu, you may not have an active Office 365 subscription.

Office 365 subscribers with Business or Volume license subscription
The procedure for changing your Channel is under the control of your IT Department. They can designate certain users as being eligible for different Channels, however. This process is described at 

In general, you will be asked to download the Office Deployment Tool from This includes a Setup.exe and a Configuration.xml file that you put in a folder you can navigate to easily. You will then need to manually edit the Configuration.xml file as discussed in the sidebar at right. Finally, you will open a Command window, and execute the following commands from the C: prompt:
cd /d C:\SomePath\MyDesignatedFolder
setup.exe /configure Configuration.xml

Edit your Configuration.xml file using Notepad

The version shown below is for 64-bit Office obtained on a volume license. It assumes there is no Visio or Project on the computer. For a complete guide on the various options for the configuration file, see 
    <Add OfficeClientEdition="64" Channel="InsiderFast">
         <Product ID="O365ProPlusRetail">
               <Language ID="en-us" />

Open in new window

Setting Office Click to Run Channel via the Registry

I found that using the configuration file and Office Setup.exe wasn't working (error message each time I tried) for my Enterprise E3 subscription to Office 365. What did work was adding a key to the registry using Regedit.exe.

There are two different approaches you can use. One updates the Channel Group Policy setting, while the other creates the UpdateBranch registry value. Both are discussed on   I used the second approach, as described below.

Here is the registry key to choose a channel:
Value name: UpdateBranch
Value type: REG_SZ
The bold part of the registry key already existed. To this, I needed to add keys successively for Office, 16.0, Common and OfficeUpdate. After the last key in that chain was added, I then added the value UpdateBranch. When done, it should look like the screenshot at left.

Set the value to:
InsiderFast       for Office Insider
Insiders       for Monthly Channel (Targeted)
Current       for Monthly Channel
FirstReleaseDeferred       for Semi-annual Channel (Targeted)
Deferred       for Semi-annual Channel
Avatar of Lee W, MVP
Lee W, MVP
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I would add one more important piece if you are trying to run this from Win7.  You may get all kinds of different errors, such as "An error occurred in the secure channel support", as CNBC now requires TLS 1.2.  However, downloading and running an EasyFix from Microsoft will set the default protocol to TLS1.2 for you:
Avatar of reallygorgo


Thanks to all who replied; I learned a lot from your answers. Experts Exchange has changed the way that points are assigned since I last used it. I hope everyone got rewarded appropriately.

Lee's solution was the most straightforward. Thanks for that. Neeraj's solution was the most elegant I think, though I had to review regular expression syntax to understand it.

Bill Bach's warning about Win 7  was appreciated. Luckily I also have a Win 10 machine in the house.

I recently subscribed to Office 365 but I am unlikely to join the Insider channel, so I would have to wait to use byundt's approach.

In the end I went with a Yahoo-based approach after all. It is working fine but I am a bit leery that they might arbitrarily change the protocol again.

In the future I may come back to try cnbc or I may go with a Google sheets approach if Yahoo changes things again.

Thanks again to everyone who replied.

You're welcome Dave!