CODEWITHSUNDEEP
excelpowerbi
Madan Mohan Verma
Madan Mohan Verma

Reputation: 13

Excel Power Query DataSource.Error: The underlying connection was closed: An unexpected error occurred on a receive

I have created a web query in Office Excel 365 which is given as under:

Name of the Query is LivSTK

let

    Source = Json.Document(Web.Contents("https://beta.nseindia.com/api/equity-stockIndices?index=SECURITIES%20IN%20F%26O")),
    data = Source[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"symbol", "identifier", "series", "open", "dayHigh", "dayLow", "lastPrice", "previousClose", "change", "pChange", "totalTradedVolume", "totalTradedValue", "lastUpdateTime", "yearHigh", "yearLow", "nearWKH", "nearWKL", "perChange365d", "date365dAgo", "chart365dPath", "date30dAgo", "perChange30d", "chart30dPath", "chartTodayPath", "meta"}, {"symbol", "identifier", "series", "open", "dayHigh", "dayLow", "lastPrice", "previousClose", "change", "pChange", "totalTradedVolume", "totalTradedValue", "lastUpdateTime", "yearHigh", "yearLow", "nearWKH", "nearWKL", "perChange365d", "date365dAgo", "chart365dPath", "date30dAgo", "perChange30d", "chart30dPath", "chartTodayPath", "meta"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Column1",{{"symbol", Order.Ascending}}),
    #"Expanded meta" = Table.ExpandRecordColumn(#"Sorted Rows", "meta", {"companyName", "industry"}, {"companyName", "industry"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded meta",{{"open", type number}, {"dayHigh", type number}, {"dayLow", type number}, {"lastPrice", type number}, {"previousClose", type number}, {"change", type number}, {"pChange", type number}, {"totalTradedVolume", type number}, {"totalTradedValue", type number}, {"yearHigh", type number}, {"yearLow", type number}, {"nearWKH", type number}, {"nearWKL", type number}, {"perChange365d", type number}, {"perChange30d", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"identifier", "series", "lastUpdateTime", "date365dAgo", "chart365dPath", "date30dAgo", "chart30dPath", "chartTodayPath"}),
    #"Rounded Off" = Table.TransformColumns(#"Removed Columns",{{"open", each Number.Round(_, 2), type number}, {"dayHigh", each Number.Round(_, 2), type number}, {"dayLow", each Number.Round(_, 2), type number}, {"lastPrice", each Number.Round(_, 2), type number}, {"previousClose", each Number.Round(_, 2), type number}, {"change", each Number.Round(_, 2), type number}, {"pChange", each Number.Round(_, 2), type number}, {"totalTradedVolume", each Number.Round(_, 2), type number}, {"totalTradedValue", each Number.Round(_, 2), type number}, {"yearHigh", each Number.Round(_, 2), type number}, {"yearLow", each Number.Round(_, 2), type number}, {"nearWKH", each Number.Round(_, 2), type number}, {"nearWKL", each Number.Round(_, 2), type number}, {"perChange365d", each Number.Round(_, 2), type number}, {"perChange30d", each Number.Round(_, 2), type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Rounded Off",{{"symbol", "Symbol"}, {"open", "Open"}, {"dayHigh", "High"}, {"dayLow", "Low"}, {"lastPrice", "LTP"}, {"previousClose", "Prev Close"}, {"change", "CHG"}, {"pChange", "CHG %"}, {"totalTradedVolume", "VOL"}, {"totalTradedValue", "VAL"}, {"yearHigh", "52W High"}, {"yearLow", "52W Low"}, {"nearWKH", "CHG % 52W H"}, {"nearWKL", "CHG % 52W L"}, {"perChange365d", "CHG % 365D"}, {"perChange30d", "CHG % 30D"}, {"companyName", "Co Name"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Time Stamp", each getLivStkTimeStmp ()),
    #"Expanded Time Stamp" = Table.ExpandTableColumn(#"Added Custom", "Time Stamp", {"Time Stamp"}, {"Time Stamp.1"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Time Stamp",{{"Time Stamp.1", "Time Stamp"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Time Stamp", type datetime}})
in
    #"Changed Type1"

Under the Response Headers of the Codes I have found the followings out of others :

content-encoding: gzip content-length: 20707 content-type: application/json; charset=utf-8 date: Mon, 30 Dec 2019 05:01:25 GMT

Now the problem is I am unable to get the data when I start excel and refresh the above query. It shows the following error :

DataSource.Error: The underlying connection was closed: An unexpected error occurred on a receive. Details: https://beta.nseindia.com/api/equity-stockIndices?index=SECURITIES%20IN%20F%26O

This, strangely, gets refreshed once the time in my system is 12:00. I am unable to find the solution, as I am not a technical person and donot have knowledge of code writing.

Please advise suitable settings. Thanks

Upvotes: 1

Views: 5818

Answers (1)

Suresh Jaganathan
Suresh Jaganathan

Reputation: 537

You need add Headers and Cookies to get rid of this error. 

cookiestr = "get the value of bm_sv cookie from browser"
Source = Json.Document(Web.Contents("https://beta.nseindia.com/api/equity-stockIndices?index=SECURITIES%20IN%20F%26O", [Headers =[#"Accept-Language"="en-US,en;q=0.9,ta;q=0.8,te;q=0.7",#"Accept-Encoding"="gzip, deflate",#"User-Agent"="Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.88 Safari/537.36", Cookie=cookiestr]])),

Refer this video for detailed explanation - Excelling Trade

Upvotes: 1

Related Questions