Power Query to download historical stock data from Yahoo Finance

[sam007]

I am using Power Query to automatically download historical stock price data from Yahoo! Finance into Excel. The URL is Invesco QQQ Trust, Series 1 (QQQ) Stock Historical Prices & Data - Yahoo Finance

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.

 

[sandy666]

maybe

let
    Source = Web.Page(Web.Contents("https://finance.yahoo.com/quote/QQQ/history/")),
    Data2 = Source{2}[Data],
    Replace = Table.ReplaceValue(Data2,",","",Replacer.ReplaceText,{"Open", "High", "Low", "Close*", "Adj Close**", "Volume"}),
    Type = Table.TransformColumnTypes(Replace,{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close*", type number}, {"Adj Close**", type number}, {"Volume", Int64.Type}})
in
    Type

 

[alansidman]

crossposted: Power Query to download historical stock data from Yahoo Finance

 

[Fluff]

@sam007
While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.