Is there a workaround for "Skip Test Connection"?

[Anonymous]

Hi folks,

I am trying to import bulk stock data for my research (+1k stocks, live and historical) but I am blocked by the connection test since my root url is not valid. It works with a single input or if I choose credentials manually but I cant do it for the bulk data. Good old "Skip Test Connection" used to solve this issue. Is there a way to circumvent this? Here is my code:

(GetData) =>

let
Source = Csv.Document(Web.Contents("https://query1.finance.yahoo.com/v7/finance/download/"
, [RelativePath = Text.From(GetData) & "?period1=" & Number.ToText(Number.RoundUp((Duration.TotalSeconds((DateTime.LocalNow())- #datetime(1970, 1, 1, 0, 0, 0) - #duration(366, 0, 0, 0))),0)) & "&period2=" & Number.ToText(Number.RoundUp((Duration.TotalSeconds((DateTime.LocalNow())- #datetime(1970, 1, 1, 0, 0, 0))),0)) & "&interval=1d&events=history"]),[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Change Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Change Type", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Adj Close", type number}, {"Volume", Int64.Type}})
in
#"Changed Type"

Much appreciated!

[camargos88 · Accepted solution]

Hi @Anonymous ,

I have a similiar solution for that, just try pasting it on Advanced Editor:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PZdZluQoDEX3Ut/dvQcGYxwGTAIeIuvU/rfReeJd6idwGNDwJD3Jv3//yj2MX3/++f3LmJr0kL/L5yHYz2Lb/q+e1uNY09+nz8OrvLTW/Fmvz2/V5lniZ93KcNr9/izZfBbpjV7v2q6rYfmsfuvSbnR1P7S91M/qutOLEtKjh8tL7J0l+Dlkksuua8d4K+HG6oi7dNfmt/43rhxdB7LDOg9IeRU2ZZGklHRzZOzLKLAg8QbVO8iPozlgdkjaJWmzGdAqqsDn0Ll1S7LFXKt0jUc7adMqBT42veW+le85S3y354PvAvjLgdM5tBFAHo+yT4raKjtdHFLgNuk5bl2vuuYMej16su5dDfm9rrrpkPCTHqdMF2TTTy8g/KnMMEA/XpJjygDsRwe33pR3dtuUsF0ugLjdi/a/R5+eKkoFH/pbqpavDR1ZttikDUch6P7ZLdcF/VjJGrljO0LGhaMOCOdGlfQ9W2JECHVsrYVqahgDtLXoXDwFwDZuQi25g1zbUiZDojay7DYU0aJrbQHZZaYmKddd1Akv3EqX3M7rski/TzKwjUIFStyj44ukpubICMzPlPBtZ3l2UD4oAS/cGv+XMUtBfvQmoB40tFm2qyfXIQ+TdKBCBAtLlt2BVLVSfy9QTt6o7VP6VvyS9Fi/tN2URG500Z3X/gLYIYH933QP8nuX9j5gxYXokh3RGdAIJKeoZrEGzmmkbe6SfDTY6GoguScQqItCCBk4PE1kjoNEPRJNlSuQsblnXcjkXAB6gLwFYLNJTXWGlKCAalNNLPSNN6n7ApRvK4UOzDf/UCMXtYFA8w3RmvekgS6gBxSdoEW3ND3ELlQqbelJRVeXWeWeOvq6iAjUm7T2AreNG+6rsDqRSZb81O6VjhkGTPGlk1oA5YeQDQYkL2rW0FjcuAxJvVNkSM87nUQm9VlzsiGd+ICzecCm1IrLK6ssItDezOY/1T9w5LhImagiDIBSRgMU2KF1rWsip+js+/KmoVTqKkIj997ng5S/JIF/u6HeCLAbRg/pmrYV2bIEBfKCQJ3BSUNW1Ua+bopYaFDQsNQYaCLIeerA6pylxYxx0xWfjeaT3WRnHbFW2RZbotydXqS3dO1dpRCpFWcJTaxUWwXz/gKAi8Z2v8kAWunhIReTJoU/dH8mL9mU/NGpb0m+PTk0Ow4mdNLSDLn52iMqk0y5ZwtazE5dKk2fNxT7KOqJ0LgfusMyup2PG7rgnABRFgiweCNmXW/6zMmIFCTKyse10d40ggZ8SYvenl7H/MqAmKiksBH145Fz2/LMTJX88J+luGhRZxpy84d2SDF7yKhv27R1zfokS1fSPdD2yjCKxle7JDUe0O4FuRUhE5HQ34HET1RUEyAHM6Ohx5RL9/cseRwHtUHk5NPTaB9NwuPsUykh9AIVxrwHY61hHCkygjKvu4wOx0O/CvSAnZX/EaY7oxS+bIReNzi+vfC32pkyMmmZE83ZmAhgKWblWxWVycG7WSpMp6+NLhj7xqRGKG6+OviscXSnQr15OPg0c4JsQMPggzm1MZK/SoUNLI3PoCm9LyynEW/q+iFs2ljmrLZ/UbrAxjSVmT7GM5uSbAsWPOOkcLqRE5g/H1Kz27+LVFyFHholcn6S3PPTgQHNwBpuzoWYGmDTcqepQnTTDnC6qZZCu7RufmRygBqttw58b4ycOcrWBkmURH8LiXwMpswOJiU7TPwDmKDutNCM+QXSiyTRT6ZSCYzLxKwnQsOwv/G5mgiZZ54tN2MkDH+W/BdpYpIIycwnm6ghamVQ6CRqvejCFyg0BYhIFged73RCWvEJ9/60N8l73bpv6sxb0nqopFfmGFqX79OfX3/+/A8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Stock = _t]),
#"Replaced Value" = Table.ReplaceValue(Source,".","-",Replacer.ReplaceText,{"Stock"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Stock", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "YahooStock", each try Csv.Document(Web.Contents("https://query1.finance.yahoo.com/v7/finance/download/"
, [RelativePath = [Stock] & "?period1=" & Number.ToText(Number.RoundUp((Duration.TotalSeconds((DateTime.LocalNow())- #datetime(1970, 1, 1, 0, 0, 0) - #duration(366, 0, 0, 0))),0)) & "&period2=" & Number.ToText(Number.RoundUp((Duration.TotalSeconds((DateTime.LocalNow())- #datetime(1970, 1, 1, 0, 0, 0))),0)) & "&interval=1d&events=history"]),[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]) otherwise null),
#"Expanded YahooStock" = Table.ExpandTableColumn(#"Added Custom", "YahooStock", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}),
#"Added Custom1" = Table.AddColumn(#"Expanded YahooStock", "Header", each try Date.FromText([Column1]) otherwise null),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Header] <> null)),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1", "Date"}, {"Column2", "Open"}, {"Column3", "High"}, {"Column4", "Low"}, {"Column5", "Close"}, {"Column6", "Adj Close"}, {"Column7", "Volume"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Header"})
in
#"Removed Columns"

I noted that some stock codes are wrong, maybe it can return some kind of error or null (once I tried to check it).

Basically, I just created a table with the stock names and a custom column to get all the data for each one.

I hope it helps you,

Ricardo

[Anonymous]

Hi @camargos88 ,

Thanks for the solution. It is exactly what I was looking for.

I run into the same "edit credentials" problem once in a while (especially when I am editing the query) but once it works, it works. 

One small follow-up: How did you hash all the stock codes into that text? So that I can apply my own stock selection into the query.

Thanks a lot for the help!

[camargos88]

@Anonymous ,

I just got in on google and pasted on new table. 

But I have projects which I get similar data from google sheets, so I just need to add or remove data from spreadsheet and PBI runs automatically.

https://docs.microsoft.com/en-us/power-bi/desktop-enter-data-directly-into-desktop

Ricardo

[Anonymous]

@camargos88 
Unfortunately I cant introduce new data in the query editor, then I run into "credentials" problem again. The only way is to introduce the data at the beginning. Could you please ellaborate how you encoded your text for the stocks? This part:

PZdZluQoDEX3Ut/dvQcGYxwGTAIeIuvU/rfReeJd6idwGNDwJD3Jv3//yj2MX3/++f3LmJr0kL/L5yHYz2Lb/q+e1uNY09+nz8OrvLTW/Fmvz2/V5lniZ93KcNr9/izZfBbpjV7v2q6rYfmsfuvSbnR1P7S91M/qutOLEtKjh8tL7J0l+Dlkksuua8d4K+HG6oi7dNfmt/43rhxdB7LDOg9IeRU2ZZGklHRzZOzLKLAg8QbVO8iPozlgdkjaJWmzGdAqqsDn0Ll1S7LFXKt0jUc7adMqBT42veW+le85S3y354PvAvjLgdM5tBFAHo+yT4raKjtdHFLgNuk5bl2vuuYMej16su5dDfm9rrrpkPCTHqdMF2TTTy8g/KnMMEA/XpJjygDsRwe33pR3dtuUsF0ugLjdi/a/R5+eKkoFH/pbqpavDR1ZttikDUch6P7ZLdcF/VjJGrljO0LGhaMOCOdGlfQ9W2JECHVsrYVqahgDtLXoXDwFwDZuQi25g1zbUiZDojay7DYU0aJrbQHZZaYmKddd1Akv3EqX3M7rski/TzKwjUIFStyj44ukpubICMzPlPBtZ3l2UD4oAS/cGv+XMUtBfvQmoB40tFm2qyfXIQ+TdKBCBAtLlt2BVLVSfy9QTt6o7VP6VvyS9Fi/tN2URG500Z3X/gLYIYH933QP8nuX9j5gxYXokh3RGdAIJKeoZrEGzmmkbe6SfDTY6GoguScQqItCCBk4PE1kjoNEPRJNlSuQsblnXcjkXAB6gLwFYLNJTXWGlKCAalNNLPSNN6n7ApRvK4UOzDf/UCMXtYFA8w3RmvekgS6gBxSdoEW3ND3ELlQqbelJRVeXWeWeOvq6iAjUm7T2AreNG+6rsDqRSZb81O6VjhkGTPGlk1oA5YeQDQYkL2rW0FjcuAxJvVNkSM87nUQm9VlzsiGd+ICzecCm1IrLK6ssItDezOY/1T9w5LhImagiDIBSRgMU2KF1rWsip+js+/KmoVTqKkIj997ng5S/JIF/u6HeCLAbRg/pmrYV2bIEBfKCQJ3BSUNW1Ua+bopYaFDQsNQYaCLIeerA6pylxYxx0xWfjeaT3WRnHbFW2RZbotydXqS3dO1dpRCpFWcJTaxUWwXz/gKAi8Z2v8kAWunhIReTJoU/dH8mL9mU/NGpb0m+PTk0Ow4mdNLSDLn52iMqk0y5ZwtazE5dKk2fNxT7KOqJ0LgfusMyup2PG7rgnABRFgiweCNmXW/6zMmIFCTKyse10d40ggZ8SYvenl7H/MqAmKiksBH145Fz2/LMTJX88J+luGhRZxpy84d2SDF7yKhv27R1zfokS1fSPdD2yjCKxle7JDUe0O4FuRUhE5HQ34HET1RUEyAHM6Ohx5RL9/cseRwHtUHk5NPTaB9NwuPsUykh9AIVxrwHY61hHCkygjKvu4wOx0O/CvSAnZX/EaY7oxS+bIReNzi+vfC32pkyMmmZE83ZmAhgKWblWxWVycG7WSpMp6+NLhj7xqRGKG6+OviscXSnQr15OPg0c4JsQMPggzm1MZK/SoUNLI3PoCm9LyynEW/q+iFs2ljmrLZ/UbrAxjSVmT7GM5uSbAsWPOOkcLqRE5g/H1Kz27+LVFyFHholcn6S3PPTgQHNwBpuzoWYGmDTcqepQnTTDnC6qZZCu7RufmRygBqttw58b4ycOcrWBkmURH8LiXwMpswOJiU7TPwDmKDutNCM+QXSiyTRT6ZSCYzLxKwnQsOwv/G5mgiZZ54tN2MkDH+W/BdpYpIIycwnm6ghamVQ6CRqvejCFyg0BYhIFged73RCWvEJ9/60N8l73bpv6sxb0nqopFfmGFqX79OfX3/+/A8=

[camargos88]

@Anonymous ,

I just input on "Enter Data" option. 

Where do the stocks names come from ? Spreasheets ??

Ricardo

[Anonymous]

I see. I tried to add data after the query which didnt work. I just saw that when you add the data first, PBI already encodes and decodes so I just paste the remainder of your formula then worked. 

Thanks a lot for the help @camargos88