SOLVED

Historical Stock Prices into Excel from Yahoo! Finance

%3CLINGO-SUB%20id%3D%22lingo-sub-1459571%22%20slang%3D%22en-US%22%3EHistorical%20Stock%20Prices%20into%20Excel%20from%20Yahoo!%20Finance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1459571%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20Power%20Query%20to%20automatically%20download%20historical%20stock%20price%20data%20from%20Yahoo!%20Finance%20into%20Excel.%26nbsp%3B%20The%20URL%20is%20%3CA%20href%3D%22https%3A%2F%2Ffinance.yahoo.com%2Fquote%2FQQQ%2Fhistory%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Ffinance.yahoo.com%2Fquote%2FQQQ%2Fhistory%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20populates%20a%20worksheet%20with%20a%20year's%20worth%20of%20stock%20prices%20with%20the%20following%20columns%3A%20Date%2FOpen%2FHigh%2FLow%2FClose%2FAdj%20Close%2FVolume.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20try%20to%20build%20a%20simple%20line%20chart%20using%20the%20data%2C%20the%20price%20column%20data%20is%20not%20recognized.%20So%20the%20Y-axis%20of%20the%20chart%20returns%20no%20values.%20I%20changed%20the%20format%20of%20the%20columns%20to%20%22Number%22%20but%20it%20doesn't%20make%20any%20difference.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20have%20any%20solutions%20or%20workarounds.%20I%20attached%20a%20copy%20of%20the%20spreadsheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1459571%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1459592%22%20slang%3D%22en-US%22%3ERe%3A%20Historical%20Stock%20Prices%20into%20Excel%20from%20Yahoo!%20Finance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1459592%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F697964%22%20target%3D%22_blank%22%3E%40Sam_S007%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20since%20all%20data%20returned%20is%20texts%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3E%3D%20Table.TransformColumnTypes(Data2%2C%7B%7B%22Date%22%2C%20type%20date%7D%2C%20%7B%22Open%22%2C%20type%20text%7D%2C%20%7B%22High%22%2C%20type%20text%7D%2C%20%7B%22Low%22%2C%20type%20text%7D%2C%20%7B%22Close*%22%2C%20type%20text%7D%2C%20%7B%22Adj%20Close**%22%2C%20type%20text%7D%2C%20%7B%22Volume%22%2C%20type%20text%7D%7D)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EPlease%20change%20in%20Power%20Query%20on%20decimal%20numbers%20where%20applicable.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1459630%22%20slang%3D%22en-US%22%3ERe%3A%20Historical%20Stock%20Prices%20into%20Excel%20from%20Yahoo!%20Finance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1459630%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20wondering%20why%20the%20first%20column%20(the%20Date%20column)%20is%20recognized%20properly%20as%20a%20date%20column%20while%20the%20other%206%20columns%20are%20only%20recognized%20as%20text%3F%26nbsp%3B%20Is%20there%20any%20way%20to%20change%20the%20format%20of%20the%20price%20columns%20from%20text%20to%20numbers%20without%20VBA%20or%20any%20programming%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1459705%22%20slang%3D%22en-US%22%3ERe%3A%20Historical%20Stock%20Prices%20into%20Excel%20from%20Yahoo!%20Finance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1459705%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F697964%22%20target%3D%22_blank%22%3E%40Sam_S007%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESure%2C%20just%20open%20the%20query%20on%20editing%2C%20find%20Change%20Type%20step%2C%20stay%20on%20it%2C%20select%20proper%20column%20and%20apply%20Decimal%20Number%20format%2C%20close%20the%20query.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20did%20that%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1459708%22%20slang%3D%22en-US%22%3ERe%3A%20Historical%20Stock%20Prices%20into%20Excel%20from%20Yahoo!%20Finance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1459708%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Sergei%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%20That%20works%20perfectly.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESam%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1460690%22%20slang%3D%22en-US%22%3ERe%3A%20Historical%20Stock%20Prices%20into%20Excel%20from%20Yahoo!%20Finance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1460690%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F697964%22%20target%3D%22_blank%22%3E%40Sam_S007%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESam%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I am using Power Query to automatically download historical stock price data from Yahoo! Finance into Excel.  The URL is https://finance.yahoo.com/quote/QQQ/history/

 

It populates a worksheet with a year's worth of stock prices with the following columns: Date/Open/High/Low/Close/Adj Close/Volume.

 

When I try to build a simple line chart using the data, the price column data is not recognized. So the Y-axis of the chart returns no values. I changed the format of the columns to "Number" but it doesn't make any difference. 

 

Does anyone have any solutions or workarounds. I attached a copy of the spreadsheet.

5 Replies
Highlighted

@Sam_S007 

That's since all data returned is texts

= Table.TransformColumnTypes(Data2,{{"Date", type date}, {"Open", type text}, {"High", type text}, {"Low", type text}, {"Close*", type text}, {"Adj Close**", type text}, {"Volume", type text}})

Please change in Power Query on decimal numbers where applicable.

Highlighted

@Sergei Baklan 

 

Just wondering why the first column (the Date column) is recognized properly as a date column while the other 6 columns are only recognized as text?  Is there any way to change the format of the price columns from text to numbers without VBA or any programming?

 

Thanks!

Highlighted
Best Response confirmed by Sam_S007 (Occasional Contributor)
Solution

@Sam_S007 

Sure, just open the query on editing, find Change Type step, stay on it, select proper column and apply Decimal Number format, close the query.

 

I did that in attached file.

Highlighted

@Sergei Baklan 

 

Hi Sergei,

 

Thanks! That works perfectly. 

 

Sam

 

Highlighted