Yahoo Finance API for Stock Quotes Changed - Page 6
Yahoo Finance API for Stock Quotes Changed
Thanks Thanks:  0
Likes Likes:  0
Page 6 of 7 FirstFirst ... 4567 LastLast
Results 51 to 60 of 61

Thread: Yahoo Finance API for Stock Quotes Changed

  1. #51
    Board Regular
    Join Date
    Aug 2009
    Posts
    1,335
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Yahoo Finance API for Stock Quotes Changed

     
    Having my doubts now.... seen a lot more worrying comments.

  2. #52
    New Member
    Join Date
    Nov 2017
    Location
    Conte Subdivision, Tres de Mayo, Digos City, Davao del Sur Philippines
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Yahoo Finance API for Stock Quotes Changed

    Quote Originally Posted by jonathanwang003 View Post
    Hello world,

    Yahoo Finance has changed their API today without any information on how we can update. The new URL uses Unix Timecoding for dates.

    Here's a sample URL for the S&P500. If you copy this and paste it into your browser, it will open the CSV with historical quotes.

    https://query1.finance.yahoo.com/v7/finance/download/^GSPC?period1=1459468800&period2=1494028800&interval=1d&events=history&crumb=yt6hjEL5Tx8


    How I've modified this with VBA is using the Qurl below. I just need 3 variables (Symbol, StartDate, and EndDate) I replaced the old Qurl which stopped working today with this Qurl code but I keep getting an error on the second to last line where it refreshes the BackgroundQuery.

    What the code does is constructs the URL, opens it and puts its data into the current workbook on a specific tab and cell.


    Code:
    Qurl = "https://query1.finance.yahoo.com/v7/finance/download/" & Symbol
        Qurl = Qurl & "?period1=" & (StartDate - DateSerial(1970, 1, 1)) * 86400 _
            & "&period2=" & (EndDate - DateSerial(1970, 1, 1)) * 86400 & "&interval=1d&events=history&crumb=yt6hjEL5Tx8"
    
         With HistoricalPrices.QueryTables.Add(Connection:="URL;" & Qurl, Destination:=PasteDest)
            .BackgroundQuery = False
            .TablesOnlyFromHTML = True
            .Refresh BackgroundQuery:=False
            .SaveData = True
    
        End With

    I've tested the URL above, I've ensured it matches what the code generates, I just don't know why it gives me an error.
    I heard that Yahoo Finance API has benn discontinued.
    MarketXLS would be an alternative. It has regular updates and custumer support unlike Yahoo.
    It cost a little but it will save you more time.
    I hope it would help you.

  3. #53
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    1,861
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Yahoo Finance API for Stock Quotes Changed

    Hi Harold,

    Welcome to the MrExcel Forum.

    I have had some experience with MarketXLS, but I am sure someone, somewhere, had to have said, "why pay, when you can ride for free!"
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  4. #54
    Board Regular
    Join Date
    Aug 2009
    Posts
    1,335
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Yahoo Finance API for Stock Quotes Changed

    Quote Originally Posted by igold View Post
    Hi Harold,

    Welcome to the MrExcel Forum.

    I have had some experience with MarketXLS, but I am sure someone, somewhere, had to have said, "why pay, when you can ride for free!"
    I've spent the weekend fretting over this and i've found a free solution that doesn't even need vba to get live prices imported every minute.

    Essentially, you create a sheet in google docs, build a table of the data you need using the googlefinance() function, publish it to web and then use that URL to 'get data from web' into excel.

    Works with 2007. As far as I can tell, once you've published the google sheet to web it seems to keep updating the web page even if the sheet is closed. Even if I'm wrong you just need to leave it open. If you set excel's connection properties to refresh in the background automatically every minute it all seems to work like a dream....

    I'm using it for live data but i think it works for historical too.

    The only thing I can't get from it is the exchange the shares are on.

    ....and the best bit? It doesn't use Yapoo.

  5. #55
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    1,861
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Yahoo Finance API for Stock Quotes Changed

    @merlin777

    Correct me if I am wrong, but even if you change the Excel connection properties to refresh every minute, you are not getting fresh data every minute. Google only refreshes every five minutes, so you are getting the same "stale" information from the Google sheet for 4 out of the 5 Excel refreshes until Google refreshes it's web page once every 5 minutes...
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  6. #56
    Board Regular
    Join Date
    Aug 2009
    Posts
    1,335
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Yahoo Finance API for Stock Quotes Changed

    Quote Originally Posted by igold View Post
    @merlin777

    Correct me if I am wrong, but even if you change the Excel connection properties to refresh every minute, you are not getting fresh data every minute. Google only refreshes every five minutes, so you are getting the same "stale" information from the Google sheet for 4 out of the 5 Excel refreshes until Google refreshes it's web page once every 5 minutes...
    You may well be right, though I haven't really looked that closely. My totals seem to change with the 1 min refreshes but it could be that individual shares are update at different 5min intervals?

  7. #57
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,106
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Yahoo Finance API for Stock Quotes Changed

    If you are looking for historical data, then this site has a downloadable spreadsheet that uses Google Finance

    http://investexcel.net/multiple-stoc...der-for-excel/
    Alan Sidman
    Win 10--Office 2016

    Click below for a white paper on Data Base Design
    http://r937.com/relational.html



  8. #58
    Board Regular DRSteele's Avatar
    Join Date
    Mar 2015
    Location
    Calgary
    Posts
    1,693
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Yahoo Finance API for Stock Quotes Changed

    Well, there is some good news. Microsoft is making moves in regards to Excel and stock quotes. See here: https://excel.uservoice.com/forums/3...272f6e89d97ae7
    Last edited by DRSteele; Feb 5th, 2018 at 04:24 PM.
    Windows10, Excel 2016 (365 Insider)
    My formulas are always in
    Green
    You can get the HTML Maker: https://www.mrexcel.com/forum/excel-...ins-links.html.

  9. #59
    Board Regular
    Join Date
    Aug 2009
    Posts
    1,335
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Yahoo Finance API for Stock Quotes Changed

    Quote Originally Posted by DRSteele View Post
    Well, there is some good news. Microsoft is making moves in regards to Excel and stock quotes. See here: https://excel.uservoice.com/forums/3...272f6e89d97ae7
    fantastic. hope it'll work with 2007.

    the google sheets approach is working so well for me I might not even use the MS option.

  10. #60
    Board Regular DRSteele's Avatar
    Join Date
    Mar 2015
    Location
    Calgary
    Posts
    1,693
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Yahoo Finance API for Stock Quotes Changed

      
    Now some super news, at least at first blush. See here: https://excel.uservoice.com/forums/3...ical-data-into
    Windows10, Excel 2016 (365 Insider)
    My formulas are always in
    Green
    You can get the HTML Maker: https://www.mrexcel.com/forum/excel-...ins-links.html.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com