Hi Members, 

I am facing an issue in creating a dynamic URL to fetch market data for Indian stocks based on two variables “COMPANY” and “QUARTER” as below 

“Expression.Error: We cannot apply operator & to types Text and Number.
Details:
Operator=&
Left=https://www.bseindia.com/corporates/results.aspx?Code=
Right=540776″

The advance editor code for Table 5 in power query for excel is as below- 

let
Source = Web.BrowserContents(“https://www.bseindia.com/corporates/results.aspx?Code=“& COMPANY() &”&qtr=”& QUARTER() &””),
#”Extracted Table From Html” = Html.Table(Source, {{“Column1”, “TABLE.tablesorter > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2)”}, {“Column2”, “TABLE.tablesorter > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(1), TABLE.tablesorter > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2) + TD[colspan=””4″”]:not([rowspan]):nth-child(2):nth-last-child(1)”}, {“Column3”, “TABLE.tablesorter > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2) + TD[colspan=””4″”]:not([rowspan]):nth-child(2):nth-last-child(1)”}, {“Column4”, “TABLE.tablesorter > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2) + TD[colspan=””4″”]:not([rowspan]):nth-child(2):nth-last-child(1)”}, {“Column5”, “TABLE.tablesorter > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2) + TD[colspan=””4″”]:not([rowspan]):nth-child(2):nth-last-child(1)”}}, [RowSelector=”TABLE.tablesorter > * > TR”])
in
#”Extracted Table From Html”

The advance editor code for Query COMPANY in power query for excel is as below- 

let COMPANY = () =>
let
Source = Excel.CurrentWorkbook(){[Name=”COMPANY”]}[Content],
Column1 = Source{0}[Column1]
in
Column1
in
COMPANY

The advance editor code for Query QUARTER in power query for excel is as below- 

let QUARTER = () =>
let
Source = Excel.CurrentWorkbook(){[Name=”QUARTER”]}[Content],
Column1 = Source{0}[Column1]
in
Column1
in
QUARTER

NOTE: original URL from the website from which we retrieve Table 5 is below:

https://www.bseindia.com/corporates/results.aspx?Code=500325&qtr=105.00

Where 500325 is a scrip/Company code for particular stock/company in number format and

105.00 is a Quarter Code for that company for a particular quarter for a financial year. The quarter code is independent of stocks and based on year and quarter combination. Our target is to replace the quarter and company code in URL with the Variables as mentioned above to fetch data for various companies quarterly results from various quarters. 

Please suggest a workaround. 

Thanks in advance.

Regards, 

Pranjit Parasar

​Hi Members, I am facing an issue in creating a dynamic URL to fetch market data for Indian stocks based on two variables “COMPANY” and “QUARTER” as below “Expression.Error: We cannot apply operator & to types Text and Number.Details:Operator=&Left=https://www.bseindia.com/corporates/results.aspx?Code=Right=540776″The advance editor code for Table 5 in power query for excel is as below- letSource = Web.BrowserContents(“https://www.bseindia.com/corporates/results.aspx?Code=”& COMPANY() &”&qtr=”& QUARTER() &””),#”Extracted Table From Html” = Html.Table(Source, {{“Column1”, “TABLE.tablesorter > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2)”}, {“Column2”, “TABLE.tablesorter > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(1), TABLE.tablesorter > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2) + TD[colspan=””4″”]:not([rowspan]):nth-child(2):nth-last-child(1)”}, {“Column3”, “TABLE.tablesorter > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2) + TD[colspan=””4″”]:not([rowspan]):nth-child(2):nth-last-child(1)”}, {“Column4”, “TABLE.tablesorter > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2) + TD[colspan=””4″”]:not([rowspan]):nth-child(2):nth-last-child(1)”}, {“Column5”, “TABLE.tablesorter > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(2) + TD[colspan=””4″”]:not([rowspan]):nth-child(2):nth-last-child(1)”}}, [RowSelector=”TABLE.tablesorter > * > TR”])in#”Extracted Table From Html”The advance editor code for Query COMPANY in power query for excel is as below- let COMPANY = () =>letSource = Excel.CurrentWorkbook(){[Name=”COMPANY”]}[Content],Column1 = Source{0}[Column1]inColumn1inCOMPANYThe advance editor code for Query QUARTER in power query for excel is as below- let QUARTER = () =>letSource = Excel.CurrentWorkbook(){[Name=”QUARTER”]}[Content],Column1 = Source{0}[Column1]inColumn1inQUARTERNOTE: original URL from the website from which we retrieve Table 5 is below:https://www.bseindia.com/corporates/results.aspx?Code=500325&qtr=105.00Where 500325 is a scrip/Company code for particular stock/company in number format and105.00 is a Quarter Code for that company for a particular quarter for a financial year. The quarter code is independent of stocks and based on year and quarter combination. Our target is to replace the quarter and company code in URL with the Variables as mentioned above to fetch data for various companies quarterly results from various quarters.  Please suggest a workaround.  Thanks in advance. Regards,  Pranjit Parasar   Read More

​