Forum Discussion

rudoji's avatar
rudoji
Copper Contributor
Jul 01, 2021

Power Query returns only 100 rows

Hello, 

 

I'm trying to download historical stock price data from Yahoo! Finance using the Power Query tool however, it only ever shows me the first 100 rows. Has anyone else encountered this problem and know how to sort it?

Link to sample here: https://uk.finance.yahoo.com/quote/CARR/history?period1=1467244800&period2=1625011200&interval=1d&filter=history&frequency=1d&includeAdjustedClose=true

 

OS is Windows 10 on a Microsoft Surface Pro and running MS Office Professional Plus. 

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor

    rudoji 

    Alternatively with Excel 365:

    =STOCKHISTORY("CARR",TODAY()-10,TODAY(),0,1,0,1,2,3,4,5)

     

    • pdxtony's avatar
      pdxtony
      Copper Contributor

      Lorenzo Unfortunately the excel stock history function returns prices that are not adjusted for dividends/distributions - so you can't use it to calculate historical performance for any instrument that has any kind of distribution

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor

    rudoji 

     

    Taking a different approach you can get what you want, until Yahoo changes something...:
    1 - Use URL like: "https://query1.finance.yahoo.com/v7/finance/chart/CARR?range=1y&interval=1d&indicators=quote&includeTimestamps=true"
    2 - Convert the binary as a Json

    3 - Parse the Json - it's not a complex one :))

    The most interesting part was to figure out that the timetamps are actually Unix sec. since Jan 1st 1970

     

    Sample attached with function ParseYahooFinanceBin that does 2 & 3
    Before any question :)) please read the comments in the 2 ex. queries and the function

     

    EDIT: Forgot to say that the approach is avail. here

  • rudoji 

    Yahoo loads 100 records on the page and the rest is uploaded by java script while you scrolling the page. Power Query loads only first static data. Afraid it's not possible to load with it dynamic data as well.

Resources