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!
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Help on VBA to extract financial data from Website
0
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.