Stock price info into Excel spreadsheet

Hi,
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?
Thanks
reallygorgoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
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]
=B$1.Price
=FIELDVALUE(B$1,"Change (%)")
=FIELDVALUE(B$1,$A7)
=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 
<Configuration>
    <Add OfficeClientEdition="64" Channel="InsiderFast">
         <Product ID="O365ProPlusRetail">
               <Language ID="en-us" />
          </Product>
     </Add> 
</Configuration>

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:
HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Office\16.0\Common\OfficeUpdate
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
0
Lee W, MVPTechnology and Business Process AdvisorCommented:
At the end of the day, even the yahoo related thing (if it's the same one I used to use) is just scraping screen info).  I reviewed a few other finance sites and settled on this function.  It's slow, but it works.

Function StockQuote(Ticker)

    ' Compile the request URL with start date and end date
    'Yahoo
    'URL = "http://ichart.finance.yahoo.com/table.csv?s=" & Ticker & "&a=" & Month(StartDate) & "&b=" & Day(StartDate) & "&c=" & Year(StartDate)
    'URL = URL & "&d=" & Month(EndDate) & "&e=" & Day(EndDate) & "&f=" & Year(EndDate) & "&g=d&ignore=.csv"
    
    'URL = "http://money.cnn.com/quote/quote.html?symb=" & Ticker   'Works, but parsing is a nightmare
    'URL = "http://www.nasdaq.com/aspx/flashquotes.aspx?symbol=" & Ticker & "&selected=" & Ticker       'COSTlastsale
    URL = "http://www.cnbc.com/quotes/" & Ticker       'var quoteDataObj
    
    Set HTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0")

    HTTP.Open "GET", Url, False
    HTTP.Send
    CSV = HTTP.ResponseText

    DataRows = Split(CSV, Chr(10)) ' split the CSV into rows
    ExitRowFor = False
    For RowNumber = LBound(DataRows) To UBound(DataRows)
        If InStr(DataRows(RowNumber), "symbolInfo") Then
            Quote = DataRows(RowNumber)
            Quote = Right(Quote, Len(Quote) - InStr(Quote, "{"))
            Params = Split(Quote, """,""")
            For Param = LBound(Params) To UBound(Params)
                If InStr(Params(Param), "last"":""") Then
                    RawQuote = Params(Param)
                    Exit For
                    ExitRowFor = True
                End If
            Next
            If ExitRowFor = True Then Exit For
        End If
    Next
    StockQuote = Replace(RawQuote, "last"":""", "")
   

End Function

Open in new window

0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may also get the stock price using Lee's function and regular expression like this...

Function StockQuote(Ticker As String)
    Dim HTTP As Object
    Dim Url As String
    Dim CSV As String
    Url = "http://www.cnbc.com/quotes/" & Ticker       'var quoteDataObj
    Debug.Print Url
    Set HTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0")

    HTTP.Open "GET", Url, False
    HTTP.Send
    CSV = HTTP.ResponseText
    StockQuote = getQuote(CSV)
End Function

Function getQuote(str As String)
Dim RE As Object
Dim Matches As Object
Set RE = CreateObject("VBScript.RegExp")
With RE
    .Global = False
    .Pattern = """last"":""([\d.]+)?"","
End With
If RE.Test(str) Then
    Set Matches = RE.Execute(str)
    getQuote = Matches(0).submatches(0)
End If
End Function

Open in new window


And then you can test it like this...

Sub Test()
MsgBox StockQuote("AAPL")
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Bill BachPresident and Btrieve GuruCommented:
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:  
https://support.microsoft.com/en-us/help/3140245/update-to-enable-tls-1-1-and-tls-1-2-as-a-default-secure-protocols-in#easy
0
reallygorgoAuthor Commented:
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.

Dave
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Dave!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.