How do you automate the data pull of historical stock options for various strike prices from NSE website to excel, is it possible using VBA?
It has various dropdown to select from, and accordingly I want to pull data.
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.)
Automating data pull of historical stock options from NSE wesbite
0
Discussion
Yes. In theory it is possible. Google for "scrape web site" to get some code to start with. It will be quite a project requiring considerable VBA skill, however. In practice, it may not be possible if the site from which you wish to scrape data doesn't want to allow that. For example, the site might display data it imports from another site to which you can't get access.
Variatus (rep: 4889) Aug 6, '17 at 6:54 am
Answers
0
It is quite possible; have you tried doing it yet?
Here is an example of another macro I used for a similar issue that should get you started:
Sub ExceltoWebsiteForm()
Dim ie As Object
Dim frm As Variant
Dim element As Variant
Set ie = CreateObject("InternetExplorer.Application")
ie.navigate "http://www.website.com/"
While ie.readyState <> 4: DoEvents: Wend
'try to get form by ID
Set frm = ie.document.getElementById("form")
'try to get form by name
If frm Is Nothing Then Set frm = ie.document.getElementsByName("form").Item(0)
If frm Is Nothing Then
'error message if the form was not found
MsgBox "Form not found"
ie.Quit
Set ie = Nothing
Else
ie.Visible = True
For Each element In frm.elements
On Error Resume Next
Select Case element.Name
Case "formFieldName1": element.Value = Sheets("Sheet1").[A1]
Case "formFieldName2": element.Value = Sheets("Sheet1").[A2]
End Select
Next
'uncomment next line to submit the form
'frm.submit
End If
End Sub
Change this line:
Case "formFieldName1": element.Value = Sheets("Sheet1").[A1]
to something like this:
Case "formFieldName1": Sheets("Sheet1").[A1] = element.Value
And it should work to get a value from a form field into Excel.
For a full explanation of this macro, go to the question where I originally posted it:
Autofill text boxes in a website from excel
In the end, how you get the data from the site, depends a bit on hot it is presented wtihin the site. Either way, you need to know a little html to get it done so you can call the correct elements to get the information that you want.
You need to list more specific requirements to get more specific help with editing the macro for your needs, but I urge you to try it out yourself first.