VBA to extract data from web [javascropt]

Hello Friends,

I am here to get your help. I have tried to extract data by web-query from a website which provide daily stock data for our local market. But I failed.
I think the web source code is programmed with javasript. I cant get desired table data by web query. I am not expert in VBA, so I seek your help.
I am attaching a screenshot here. I wish to browse / view the data same in my excel workbook.

Data Link: http://www.dsebd.org/

If you select any stock from the list, it will show you the stocks current ask and bid price in a table manner. And also display today summery of selected stock.

Any kind of help appreciate me.
Thanks in Advance.

N.B: The table fully viewed while market remain open @ GMT 4:30 - 8:30 | Sunday - Thursday.

Looks pretty easy to pick up, using Power Query. The javascript on the listbox simply passes the instrument string as an argument to a php page, which is then returned. That php page has 3 tables (Buy, Sell and Price) which you can then easily import and transform.

What does your required output look like?

Here's an example of how to get the price table, for a given instrument:

Please Login or Register  to view this content.

Can you tweak that to suit your requirement? Ask for more help, if needed.

Here's an example of how you may want to use it - this has a query to retrieve all the instrument codes, and use them as a data validation source, in a list. When an instrument is selected, the queries refresh, to update the Price, Buy and Sell tables.

Helpful?

Dear Olly, thanks brother for your quick response. Its my late reply I was outside from home.
Ok, Your solution little confusion me. Actually I am not familiar with power query, is it an extra tool for data query?

Basically, I want to view/browse multiple instruments data in single excel sheet. if the solution for one instrument done by VBA with auto refresh , then I should edit it for multiple scenario.

Did you try my attached workbook?

See this link for clear instructions on how to install Power Query in Excel 2013: https://www.excelcampus.com/install-power-query/

Originally Posted by Olly

Here's an example of how you may want to use it - this has a query to retrieve all the instrument codes, and use them as a data validation source, in a list. When an instrument is selected, the queries refresh, to update the Price, Buy and Sell tables.

Helpful?

Thanks Brother, Its look like what I wish for. But its not work for me

Originally Posted by iqbal.88

Thanks Brother, Its look like what I for. But its not work for me

Have you installed the Power Query Add-In?

Power Query Installation showing Error for me

Update: I managed to install Power Query.

But it sowing some error message, when select instrument from drop down list. After sometimes it goes blank in result, No value in dropdown box.

What is the error message?

The problem isn't in the VBA code - all that is doing is triggering the query to refresh, when you change the instrument code in cell E2.
It seems that no web page is being returned for the selected instrument. However, I can't recreate your error...

Which instrument code did you select? Try opening a browser and visiting http://www.dsebd.org/bshis_new1_old.php?w=XXXX - replace XXX with the instrument code you tried. Does the page open ok?

Please try what I asked you in post #13.

Originally Posted by iqbal.88

Question:
H5:H14 and K5:K14, these range value not work in another function Like, Sum or Average
How could I call this range value to another function in different cell??

What are you trying to do?

Also, which Instrument is returning data for Buy / Sell?

Like, I want to calculate Total, & Average of buy/sell volume in different cell and count if the cell have value within range. For each instrument which is selected in drop-down cell.

Each instrument return data which has data in website.

Select the table, click TableTools | Design > Table Style Options > check "Total Row"


Can you answer my question about which instrument is returning Buy / Sell data? They all seem blank to me.

Originally Posted by Olly

Can you answer my question about which instrument is returning Buy / Sell data? They all seem blank to me.

Brother, Buy/Sell data is realtime. It returns only when market is open.
I already mention it in the bottom of my very first post. Quoted from the post
"N.B: The table fully viewed while market remain open @ GMT 4:30 - 8:30 | Sunday - Thursday."

In addition, I need to analyse / calculate some parameter based on buy/sell data. I need to reference table range or specific cell value to another formula.

Originally Posted by iqbal.88

In addition, I need to analyse / calculate some parameter based on buy/sell data. I need to reference table range or specific cell value to another formula.

It's easy to do that - the tables returned are just normal listobject tables, which you can reference like any other.

I think this thread is now closed - it's answered your question, as far as I can see. If you have other questions about formulas to analyse the data returned, that's a different requirement. I suggest you mark this thread SOLVED (please select Thread Tools from the menu link above and mark this thread as SOLVED).

Thanks.