Grab Specific data from web source code [html]

[iqbal.88]

Hello My Expert Friends,

I want some specific data to grab from a website related to our local stock market.
But I failed to grab data from there with the way of excel basic [table] webquery.
I think it can be done with Excel VBA (Macro), But I am newbie in Excel Programming.
So, I am here to hope your help.

Website: http://dsebd.org/

Data Needed in this manner:

*Last update
*DSEX Index
*DSES Index
*DS30 Index
*Total Trade
*Total Volume
*Total Value in Taka (mn)
*Issues Advanced
*Issues declined
*Issues Unchanged

Please I attached a image for your better understanding

For any help Thanks in Advance.

Index VBA.PNG

[Marc L]

Hi !

You can start to play with this :

Sub Demo1()
        Dim oDoc As Object, oElt As Object
    With CreateObject("MSXML2.XMLHttp")
        .Open "GET", "http://dsebd.org/", False
        .setRequestHeader "DNT", "1"
            On Error Resume Next
        .send
            On Error GoTo 0
        If .Status = 200 Then
                 Set oDoc = CreateObject("HTMLfile")
                     oDoc.body.innerHTML = .responseText
            For Each oElt In oDoc.getElementsByTagName("DIV")
                If oElt.ClassName = "col-xs-5 col-sm-5 LeftCol " Then
                  If oDoc.frames.clipboardData.setData("Text", oElt.innerText) Then
                     ActiveSheet.Paste Cells(3)
                     oDoc.frames.clipboardData.clearData "Text"
                  End If
                    Set oElt = Nothing
                    Exit For
                End If
            Next
                 Set oDoc = Nothing
        End If
    End With
End Sub  

Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

[iqbal.88]

A big hug & thanks for you Marc L

One request could you Please add some comments, so that I can understand better the code.

[hudson andrew]

Hi Iqbal... even I have this kind of question . But not with above criteria ... I was just going through the code and trying to understand how this work even I am new to this VBA... below code is giving me error message .. can you advice please .

[Marc L]

Hi !

Thanks for the rep' !

To better understand the procedure, you must foresee the webpage elements using your webbrowser inner inspector tool !
As piloting a webbrowser is often the slowest way and tricky, better is to reproduce same request used by webbrowsers
as you can see within the webbrowser inner inspector tool in its Network section …

So the procedure uses a request object to load the webpage code to copy it into another HTML object,
scans DIV elements for a specific ClassName and once found copies its text to clipboard to paste it into active worksheet.

A must see : Web Scraping tutorial …

[iqbal.88]

@hudson, This works for me good in a specific sheet, like Sheet1, Sheet2, etc. But get error in module.
I am also new in VBA, just trying to go through the code.

[Marc L]

I just update code to run from a standard module with active worksheet …

As a reminder, this is not a standard VBA code but OOP 'cause it uses external Windows objects !
Source documentation / help is often on MSDN website …
Requires a bit of HTML skills, observation but a lot !

[iqbal.88]

To run the code automatically in every 1 minute, I added the code just before

Please Login or Register  to view this content.

Please Login or Register  to view this content.

work first time and next return with error
Macro Automation Error.PNG

[hudson andrew]

thank you all for you valuable time in sharing this with me .. can I replace above website with my website and then run.. unfortunately my website a login option ..

[iqbal.88]

thank you all for you valuable time in sharing this with me .. can I replace above website with my website and then run.. unfortunately my website a login option ..

I it should not be......
I think DSE and BSE/NSE webpage coding pattern might not be same....
You should analyse your website code first....... then replace/reconstruct the VBA according to...

[Marc L]

work first time and next return with error

It's not a surprise if code is located in a worksheet module 'cause I don't see its reference within error message.
In the case you use OnTime method - we just can try to guess as you did not well post your added code - you must
specify the worksheet module before procedure name or just move procedure to a standard module …

[iqbal.88]

Thanks Buddy its working fine in standard module run.

specify the worksheet module before procedure name

But sorry I can't understand what do you mean by that...

[Marc L]

If procedure is located in a worksheet module,
using OnTime method needs this worksheet CodeName reference before procedure name.

For example "Sheet1.Demo1" …

[iqbal.88]

Hello My Expert Friends,

I need your help... Again
From the same website I need more some data to scrape simultaneously.
Which are the multiple Company info, in various categories.
Single Company Data aggregated in Single page/link.

There are about 300+ pages(link) Like,

dsebd.org/displayCompany.php?name=BATBC

Basically There is no coding structure difference among the pages, even in URL also.

If I change the above URL to

dsebd.org/displayCompany.php?name=ABBANK

, just give the change in "BATBC" to "ABBANK",
I can surely browse the another company page.

So I need to make a Macro / or VBA Program, to automate the process for coping the specific data from the individual company Profile.


The Needed Data Highlighted in Screenshot
Company Information_BATBC.png

Each Segment of data Organize in in row basis for the respective company
Table Format.PNG

All the company unique ID listed in the attachment file(.txt)

How do I Start???

TIA,

[Marc L]

Which attachment ?!

The way could be the same : a request to import webpage code and extraction from a HTML file …

[iqbal.88]

Which attachment ?!

The way could be the same : a request to import webpage code and extraction from a HTML file …

Sorry that time I could not upload the txt file, now I updated the post with attachment PLEASE CHECK

GIDE ME HOW COULD I DO IT....... PLEASE

[Marc L]

• Beginners way : import all data in a temp worksheet via Import from Web (QueryTable in VBA),
choose tables to import and once data are in this temp worksheet you just have to Copy / Paste
needed data and that's easy using Macro Recorder !

• For an advanced way, the start could be exactly the same as my previous code :
import webpage code via a request to a HTML file …

Then, to extract partial tables data, you need first to observe the characteristics of the tables
(ClassName, ID, Name, …) using any webbrowser inner inspector tool as explained in tutorial of post #5
and in webbrowser inner help or in Using the F12 developer tools for example …

[iqbal.88]

Hello my Friend,

Thank you again for your kind advice.
I am trying..... Now

How do I loop/run the code According to the list in .txt file, until the list is ends up??

Please Login or Register  to view this content.

TIA,

[Marc L]

Hi !

Easier if you directly import codes from text files in column A for example …

Then you could use a loop (like For … Next or Do … Loop or While … Wend) on used rows (cells) of that column as TickerName …

[iqbal.88]

Hi !

Easier if you directly import codes from text files in column A for example …

Then you could use a loop (like For … Next or Do … Loop or While … Wend) on used rows (cells) of that column as TickerName …

is it possible to import the TickerName directly from .txt file in Macro??
I am trying, but I cant solve the loop function! Can you please make it for me?

[Marc L]

You can try first to import text file with a QueryTable from Excel via Import from File …

[iqbal.88]

Please Login or Register  to view this content.

Hello Friend,
I need your help again. If I run the code once, it works fine and updating data. but when I run it second time, it seems that macro executing internally but data not refreshing or updating.
For updating second time I have to close workbook & re-open it, run macro.
Mean that macro not updating multiple time data while workbook open once.

Please I need your expert hand to solve this issue.

[Marc L]

Yes 'cause of data already in memory via Open "GET" method; try instead "POST" …

[iqbal.88]

Yes 'cause of data already in memory via Open "GET" method; try instead "POST" …

Thanks, Its work like charm, you are genius Bro.
Hey bro I wish to learn programming like you. Do you teach me??

[Marc L]

Hi !

I learn by reading inner VBA help, a bit of testing, tutorials, a bit of testing, threads from forums, a bit of testing …