Macro to get data from Webpage (only desired table)

[Andrei]

Hello,

I am trying to get one table in Excel from a webpage. The Webpage is this: http://bvb.ro/FinancialInstruments/Details/FinancialInstrumentsDetails.aspx?s=bio

I want to get also the table from "Financial" section of that webpage, not only from the default "Overview".

I created code that imports data for the symbol i input, but it gets only data from "Overview" I don't want that, i need also the table from "Financials": Annual financial information (from the website menu). I attached a photo to see exactly what table i refer to.

This is the code and the excel file is attached:

Sub Import_Data()

  ActiveSheet.Columns("A:D").Delete

'x is the simbol of the share. Ex: snp, bio

  x = Application.InputBox("Please insert the share symbol:")

  With ActiveSheet.QueryTables.Add(Connection:="URL;http://bvb.ro/FinancialInstruments/Details/FinancialInstrumentsDetails.aspx?s=" & x, Destination:=Range("$A$1"))
  '.CommandType = 0
  .Name = "FinancialInstrumentsDetails.aspx?s=bio"
  .FieldNames = True
  .RowNumbers = False
  .FillAdjacentFormulas = False
  .PreserveFormatting = True
  .RefreshOnFileOpen = False
  .BackgroundQuery = True
  .RefreshStyle = xlInsertDeleteCells
  .SavePassword = False
  .SaveData = True
  .AdjustColumnWidth = True
  .RefreshPeriod = 0
  .WebSelectionType = xlEntirePage
  .WebFormatting = xlWebFormattingNone
  .WebPreFormattedTextToColumns = True
  .WebConsecutiveDelimitersAsOne = True
  .WebSingleBlockTextImport = False
  .WebDisableDateRecognition = False
  .WebDisableRedirections = False
  .Refresh BackgroundQuery:=False
 
  End With
 
End Sub

Do you have any solution to import the table i need ?

And after that, do you have any idea of how i can get into an excel sheet that data for all companies on the website ? (some script to loop through all symbols and arrange the data into a tabel structure with columns: company, year, indicator 1, indicator 2 etc)

Thanks a lot !

 

[Deepak]

Hi,

For 1st..
Attached is the just initial approach in this regard where lot's of improvements is to be done with it.

 

[Andrei]

Wow, that is awsome !!! Where did you get from this url information ? URL;http://bvb.ro/RapoarteFinanciare/IFC_rf_fs.aspx?s=" & x & "&d=12/31/2012" ?

I want also to get the dividends from the view "Issuer Profile" and i don't know how to get the correct URL, can you help also with this ? I attached two printscreens to see what is the info i need.

Anyway, your response helps me a lot because i think i can use it to create a macro which will take data for a list of companies (this is the final purpose of this). This will be very useful because based on this data i will calculate financial indicators for all companies and i will be able to sort them based on different criteria and see which companies are performing good and are undervalued etc..

If what i have in my head will work i will post it here.

Thank you very much !!

 

[Deepak]

Hi,

If you are interested then we will do it step by step as lot's of thing to be done & compiling it in a shot may confuse you.

So, In a first approach i did.
Now, let me know what can i do in 2nd step.

 

[Andrei]

I want to have the informations as in the file "example" for all companies from the sheet "comp_list" from file "Import..." attached now.

I managed to create macro which takes data from site and copies it into a "data_base" but there a couple of problems:

1) Not all companies have data for year 2013, 2012, 2011.
2) Not all companies have the data in "Financials" in the same format (look for example at BRK and TLV as you will see 2 different formats of reporting data). Companies with this formats we will need to import them into 2 different sheets.
3) I don't know how to get the "Dividends" from the "Issuer Profile" view from website
4) The data from "Overview" is easy to get, i will use macro from post 1 of this thread.
5) After we will have all formats of data, we will make a loop to import data for all 1000 of companies into Data Base

 

[Deepak]

Ok. will check & update you soon.

 

[Deepak]

1) Yes..
2) Yes..
3) Me too
4) Ok
5) Good

As you highlighted that there are different type of format are there so simply we can loop it with a url string. Have to find some other alternative.

Conclusion : We are back to the pavilion to start the game again with other alternatives.

 

[Andrei]

I think it can be used like this.

1) Maybe it's better to use it to import just one year at once. So i will make a user form or an Add-in where the user will input the year of import. If the company has no data in that year, it will copy nothing to data base, if it has, it will copy that data.

2) I will make 3 sheets for the different types of format and before it copies the data to "Data base" it will check by the "name of first indicator" in which sheet to copy.

3) This is the single thing that i can't figure out how to get and use.. I don't know where you found the URL for importing data from "Financials" because i don't know HTML and other programming languages than VBA to check in the page code. Maybe if you can explain how you found that URL i will try to use the idea to get also the URL for dividends (data from "Issuer profile").

 

[Andrei]

If in the link: "URL;http://bvb.ro/RapoarteFinanciare/IFC_rf_fs.aspx?s=" & x & "&d=12/31/2012" ?" we put "IFB" instead of "IFC" it will take the format for banks, for example for TLV, BRD. (i figured this out by luck, i simply tried to change C with B and it worked ) ) But i didn't figure out the URL for the other format of reports, example BRK, FP etc... In this format there are a lot of companies, more than 50% of all companies.. I attached a new file with the modifications i made

 

[Deepak]

See from where i got the link & do the R&D

 

[Andrei]

Thanks a lot ! It really helped. I attached a new file with what i worked based on your code. I managed to take the data for the first 100 companies and put it in 3 different sheets based on the reporting type.

Now it remains only the problems with the dividends (to take them from "Issuer profile". Here i'm stuck) and to take also the data from "Overview" (i will work at this last one later.)

 

[Deepak]

till the time i didn't find any way to scrap the dividends.

 

[Andrei]

Hello, i managed to solve also the scraping of dividends from website. I payed a freelancer on a freelancing website. I will post the file and the code here maybe it will be useful for someone else too. @Deepak, thanks a lot for helping with this !!

Option Explicit

Public Sub getDivident()
  Dim webPAGE  As String
  Dim tableID  As String
  Dim IssuerID  As String
   
  Dim IE  As SHDocVw.InternetExplorer
  Dim TRs  As Object
  Dim TR  As Object
  Dim TDs  As Object
   
  Dim lROW  As Long
  Dim lASTROW  As Long
   
  webPAGE = "http://www.bvb.ro/FinancialInstruments/Details/FinancialInstrumentsDetails.aspx?s="
  tableID = "ctl00_body_ctl01_ctl00_gv"
  IssuerID = "ctl00_body_IFTabsControlDetails_lb5"
   
  Set IE = New SHDocVw.InternetExplorer
   
  lASTROW = Range("A" & Rows.Count).End(xlUp).Row
   
  For lROW = 2 To lASTROW
  If Cells(lROW, 3) = "" Then
  Cells(lROW, 3) = "-"
  IE.Navigate webPAGE & Cells(lROW, 1)
  While IE.Busy Or IE.ReadyState <> READYSTATE_COMPLETE: DoEvents: Wend
  IE.Document.getElementbyID(IssuerID).Click
9
  On Error Resume Next
  Set TRs = Nothing
  Set TRs = IE.Document.getElementbyID(tableID).getElementsbyTagName("tbody")(0).getElementsbyTagName("tr")
  On Error GoTo 0
  If TRs Is Nothing Then Application.Wait Now + 0.00001: GoTo 9
   
  If TRs.Length > 0 Then
  For Each TR In TRs
  Set TDs = TR.getElementsbyTagName("td")
  Debug.Print TDs(0).innertext & " =  " & Cells(lROW, 2)
  If Trim(TDs(0).innertext) = Trim(Cells(lROW, 2)) Then Cells(lROW, 3) = Replace(TDs(1).innertext, ",", "."): Exit For
  Next
  End If
  End If
  Next
   
  IE.Quit

  Set IE = Nothing
  Set TR = Nothing
  Set TDs = Nothing
  Set TRs = Nothing
   
  MsgBox "Completed", vbInformation
   
End Sub

 

[Deepak]

That's cool!!

 

[Deepak]

I was there @ "ctl00_body_IFTabsControlDetails_lb5" but couldn't manage to utilize it.

 

[Andrei]

Based on this how will be a macro which will import the "Last Price", "52 weeks high, 52 weeks low" , "Total number of shares" and "Nominal Value" from the "Overview" tab of the website ?

The code with connection works very very slow for the "Overview". I don't understand why, because for the data from "Financials" the code with "Connection" works faster than the one with Internet Explorer.

 

[Marc L]

Hi !

Piloting IE is much more slower than a QueryTable or a request …
​

 

[Andrei]

Hello again,

Regarding the import of data from webpage, the bvb.ro website was modified and they somehow "locked" the possibility to copy data

If i try to import from those links:
http://www.bvb.ro/RapoarteFinanciare/IFC_rf_fs.aspx?s=TEL&d=12/31/2014

http://www.bvb.ro/RapoarteFinanciare/C_rf_fs.aspx?s=BVB&d=12/31/2014

The amounts are not copied any more, since they cannot be copied either manualy

Could you please help with a solution to import those amounts ?

The initial code which does not work any more sample:

Sub test1()

Dim theURL As String

With ActiveSheet.QueryTables.Add( _
    Connection:="URL;http://www.bvb.ro/RapoarteFinanciare/IFC_rf_fs.aspx?s=TEL&d=12/31/2014", Destination:=Range("$A$1"))
        '.CommandType = 0
        .Name = "FinancialInstrumentsDetails.aspx?s=TEL"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

End Sub

Thanks a lot !

 

[Marc L]

There is no amount anymore, only picture when using Copy / Paste …

​