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?
Microsoft OfficeMicrosoft ExcelVBA

Avatar of undefined
Last Comment
Subodh Tiwari (Neeraj)

8/22/2022 - Mon

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.
Screenshot showing Stocks Data Type
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 https://products.office.com/en-us/office-insider?tab=Windows-Desktop 

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 https://support.office.com/en-us/article/how-office-365-commercial-customers-can-get-early-access-to-new-office-2016-features-4dd8ba40-73c0-4468-b778-c7b744d03ead?ui=en-US&rs=en-US&ad=US 

In general, you will be asked to download the Office Deployment Tool from https://www.microsoft.com/en-us/download/details.aspx?id=49117 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 https://docs.microsoft.com/en-us/deployoffice/configuration-options-for-the-office-2016-deployment-tool 
    <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 https://www.msoutlook.info/question/office-365-for-business-office-insider-fast-builds   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
Lee W, MVP

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Bill Bach

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:  

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.

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Subodh Tiwari (Neeraj)

You're welcome Dave!