Forum Discussion
Expression Error - #""Changed Type""
- 9 years ago
I got this working, the directions are less than clear.
Create all your queries using a new blank query and then open Advanced Editor and paste in the code.
Make sure your custom function is called "StockQuotes"
(GetData) => let Source = Csv.Document(Web.Contents("https://www.quandl.com/api/v3/datatables/WIKI/PRICES.csv?date.gte=20100101&date.lte=" & Number.ToText(Date.Year(DateTime.FixedLocalNow())) & Number.ToText(Date.Month(DateTime.FixedLocalNow())) & Number.ToText(Date.Day(DateTime.FixedLocalNow())) &"&ticker=" & Text.From(GetData) & "&api_key=Zx8Z9a6MLmPGvoav_-U1 "),[Delimiter=",", Columns=14, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ticker", type text},{"date", type date}, {"open", type number}, {"high", type number}, {"low", type number}, {"close", type number}, {"volume", Int64.Type}, {"ex-dividend", type number},{"split_ratio", type number},{"adj_open", type number},{"adj_high", type number},{"adj_low", type number},{"adj_close", type number},{"adj_volume", Int64.Type }}) in #"Changed Type"The next one needs to be called "Stock Tickers" (and one of their columns is named wrong, this corrects it):
let Source = Table.FromRecords({ [Ticker = "GE", Company = "General Electric Company"], [Ticker = "IBM", Company = "International Business Machines"], [Ticker = "MSFT", Company = "Microsoft Corp."] }), #"Renamed Columns" = Table.RenameColumns(Source,{{"Ticker", "Tickers"}}) in #"Renamed Columns"And this one should be called "Data Table"
let Source = #"Stock Tickers", Tickers = Source[Tickers], #"Converted to Table" = Table.FromList(Tickers, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each StockQuotes([Column1])), #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"date", "open", "high", "low", "close", "volume", "adj_close"}, {"Custom.date", "Custom.open", "Custom.high", "Custom.low", "Custom.close", "Custom.volume", "Custom.adj_close"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom1",{{"Column1", "Ticker"}, {"Custom.date", "Date"}, {"Custom.open", "Open"}, {"Custom.high", "High"}, {"Custom.low", "Low"}, {"Custom.close", "Close"}, {"Custom.volume", "Volume"}, {"Custom.adj_close", "Adj. Close"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Volume", Int64.Type}, {"Adj. Close", type number}}) in #"Changed Type"Make sure to authenticate to quandl as Anonymous.
I got this working, the directions are less than clear.
Create all your queries using a new blank query and then open Advanced Editor and paste in the code.
Make sure your custom function is called "StockQuotes"
(GetData) =>
let
Source = Csv.Document(Web.Contents("https://www.quandl.com/api/v3/datatables/WIKI/PRICES.csv?date.gte=20100101&date.lte=" & Number.ToText(Date.Year(DateTime.FixedLocalNow())) & Number.ToText(Date.Month(DateTime.FixedLocalNow())) & Number.ToText(Date.Day(DateTime.FixedLocalNow())) &"&ticker=" & Text.From(GetData) & "&api_key=Zx8Z9a6MLmPGvoav_-U1
"),[Delimiter=",", Columns=14, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ticker", type text},{"date", type date}, {"open", type number}, {"high", type number}, {"low", type number}, {"close", type number}, {"volume", Int64.Type}, {"ex-dividend", type number},{"split_ratio", type number},{"adj_open", type number},{"adj_high", type number},{"adj_low", type number},{"adj_close", type number},{"adj_volume", Int64.Type }})
in
#"Changed Type"The next one needs to be called "Stock Tickers" (and one of their columns is named wrong, this corrects it):
let
Source = Table.FromRecords({
[Ticker = "GE", Company = "General Electric Company"],
[Ticker = "IBM", Company = "International Business Machines"],
[Ticker = "MSFT", Company = "Microsoft Corp."]
}),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Ticker", "Tickers"}})
in
#"Renamed Columns"And this one should be called "Data Table"
let
Source = #"Stock Tickers",
Tickers = Source[Tickers],
#"Converted to Table" = Table.FromList(Tickers, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each StockQuotes([Column1])),
#"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"date", "open", "high", "low", "close", "volume", "adj_close"}, {"Custom.date", "Custom.open", "Custom.high", "Custom.low", "Custom.close", "Custom.volume", "Custom.adj_close"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Custom1",{{"Column1", "Ticker"}, {"Custom.date", "Date"}, {"Custom.open", "Open"}, {"Custom.high", "High"}, {"Custom.low", "Low"}, {"Custom.close", "Close"}, {"Custom.volume", "Volume"}, {"Custom.adj_close", "Adj. Close"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Volume", Int64.Type}, {"Adj. Close", type number}})
in
#"Changed Type"
Make sure to authenticate to quandl as Anonymous.
Thank you for the time you took to explain the solution. I appreciate it very much.