Power Query returns only 100 rows

%3CLINGO-SUB%20id%3D%22lingo-sub-2508285%22%20slang%3D%22en-US%22%3EPower%20Query%20returns%20only%20100%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2508285%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20download%20historical%20stock%20price%20data%20from%20Yahoo!%20Finance%20using%20the%20Power%20Query%20tool%20however%2C%20it%20only%20ever%20shows%20me%20the%20first%20100%20rows.%20Has%20anyone%20else%20encountered%20this%20problem%20and%20know%20how%20to%20sort%20it%3F%3C%2FP%3E%3CP%3ELink%20to%20sample%20here%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fuk.finance.yahoo.com%2Fquote%2FCARR%2Fhistory%3Fperiod1%3D1467244800%26amp%3Bperiod2%3D1625011200%26amp%3Binterval%3D1d%26amp%3Bfilter%3Dhistory%26amp%3Bfrequency%3D1d%26amp%3BincludeAdjustedClose%3Dtrue%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fuk.finance.yahoo.com%2Fquote%2FCARR%2Fhistory%3Fperiod1%3D1467244800%26amp%3Bperiod2%3D1625011200%26amp%3Binterval%3D1d%26amp%3Bfilter%3Dhistory%26amp%3Bfrequency%3D1d%26amp%3BincludeAdjustedClose%3Dtrue%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOS%20is%20Windows%2010%20on%20a%20Microsoft%20Surface%20Pro%20and%20running%20MS%20Office%20Professional%20Plus.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2508285%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2511865%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20returns%20only%20100%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2511865%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1093373%22%20target%3D%22_blank%22%3E%40rudoji%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYahoo%20loads%20100%20records%20on%20the%20page%20and%20the%20rest%20is%20uploaded%20by%20java%20script%20while%20you%20scrolling%20the%20page.%20Power%20Query%20loads%20only%20first%20static%20data.%20Afraid%20it's%20not%20possible%20to%20load%20with%20it%20dynamic%20data%20as%20well.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2513421%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20returns%20only%20100%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2513421%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1093373%22%20target%3D%22_blank%22%3E%40rudoji%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETaking%20a%20different%20approach%20you%20can%20get%20what%20you%20want%2C%20until%20Yahoo%20changes%20something...%3A%3CBR%20%2F%3E1%20-%20Use%20URL%20like%3A%20%22%3CA%20href%3D%22https%3A%2F%2Fquery1.finance.yahoo.com%2Fv7%2Ffinance%2Fchart%2FCARR%3Frange%3D1y%26amp%3Binterval%3D1d%26amp%3Bindicators%3Dquote%26amp%3BincludeTimestamps%3Dtrue%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fquery1.finance.yahoo.com%2Fv7%2Ffinance%2Fchart%2FCARR%3Frange%3D1y%26amp%3Binterval%3D1d%26amp%3Bindicators%3Dquote%26amp%3BincludeTimestamps%3Dtrue%3C%2FA%3E%22%3CBR%20%2F%3E2%20-%20Convert%20the%20binary%20as%20a%20Json%3C%2FP%3E%3CP%3E3%20-%20Parse%20the%20Json%20-%20it's%20not%20a%20complex%20one%20%3A))%3C%2FP%3E%3CP%3EThe%20most%20interesting%20part%20was%20to%20figure%20out%20that%20the%20timetamps%20are%20actually%20Unix%20sec.%20since%20Jan%201st%201970%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESample%20attached%20with%20function%20%3CEM%3E%3CSTRONG%3EParseYahooFinanceBin%3C%2FSTRONG%3E%3C%2FEM%3E%20that%20does%202%20%26amp%3B%203%3CBR%20%2F%3EBefore%20any%20question%20%3A))%20please%20read%20the%20comments%20in%20the%202%20ex.%20queries%20and%20the%20function%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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&fi...

 

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

3 Replies

@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.

@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&include..."
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 

Alternatively with Excel 365:

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