Help on VBA to extract financial data from Website

0

Greetings Colleagues!
I am trying to make a file where it displays financial data from this site: http://edge.pse.com.ph/companyDirectory/form.do and automatically updates into my excel file (please see attached). I am not that familiar with VBA so I am requesting some help from anyone if they can?

I have the first column with the stock codes. I need to extract data to fill in the Security Name, Close Price, Outstanding Shares, Market Capitalization and Book Value. As mentioned above, these may be extracted from here: http://edge.pse.com.ph/companyDirectory/form.do

Additionally, is there also a way that the data refreshes automatically?

Would anyone with a kind heart please assist me with the VBA for this?

Many Thanks in Advance!

Answer
Discuss

Answers

0

To do what you want in a way that looks good and functions smoothly requires A LOT of work.

I will give you a sample macro and point you to a web page that will get you started on the journey of making a fully functioning spreadsheet.

Sample Code:

Enum READYSTATE
READYSTATE_UNINITIALIZED = 0
READYSTATE_LOADING = 1
READYSTATE_LOADED = 2
READYSTATE_INTERACTIVE = 3
READYSTATE_COMPLETE = 4
End Enum

Sub ImportData()

'to refer to the running copy of Internet Explorer
Dim ie As InternetExplorer
'to refer to the HTML document returned
Dim html As HTMLDocument
'open Internet Explorer in memory, and go to website
Set ie = New InternetExplorer

ie.Visible = False
ie.navigate "http://edge.pse.com.ph/companyPage/stockData.do?cmpy_id=13&security_id=234"

'Wait until IE is done loading page
Do While ie.READYSTATE <> READYSTATE_COMPLETE
Application.StatusBar = "Working ..."
DoEvents
Loop

'show text of HTML document returned
Set html = ie.document

'output the html so you can see it
MsgBox html.DocumentElement.innerHTML

'close down IE and reset status bar
Set ie = Nothing
Application.StatusBar = ""
End Sub

This code and a full explanation of how to use it comes from this site.

You will have to do a lot of work to get this to work for you but this should get you started.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login