I use web query to download NAV form web site, i have recorded in macro. Now i want to download historical data in excel sheet form web in below mention format how can i do it through web query or macro Excel Spreadsheet:
A
B
C
1
Fund
DATE
NAV
2
Equity Growth Fund
1-Nov-09
3
Bond Fund
5-Nov-09
4
Income Fund
4-May-09
5
web site :http://www.idbifortis.com/TrackNAV.aspx?lm1=1
VBA Code:
Sub downloadnav() Worksheets("sheet1").Activate ActiveSheet.Cells.Clear With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.idbifortis.com/TrackNAV.aspx?lm1=1", Destination:=Range("A1") _ ) .Name = "TrackNAV.aspx?lm1=1" .RefreshStyle = xlInsertDeleteCells .WebFormatting = xlWebFormattingNone .WebTables = """DGNav""" .Refresh BackgroundQuery:=False End With End Sub
To get other dates for the same data (i.e. historical) you will need to send a form variable to the web query with the date value in the format required by the page: DD-MMM-YYYY. The form variable name is "TxtDate". To find out how to add post form variables in a web query, see this post.
You best approach would be to create a separate page for the web query results, loop through the dates required and with each call extract the data that you want into the desired sheet in the format that you require. For this, you will need to loop through the dates in column B using a For Each...Next loop to call the web query with each date.
Thanks for reply, but i dont have proficiency in this language, Can you please assist further, as per my understanding i modify below mention code. How can i link sheet date with vba code, how to use loop statement.
Sub downloadnav() Dim TxtDate As String Const MyUrl As String = "URL;http://www.idbifortis.com/TrackNAV.aspx?lm1=1" With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.idbifortis.com/TrackNAV.aspx?lm1=1", Destination:=Range("A1") _ ) .Name = "TrackNAV.aspx?lm1=1" .RefreshStyle = xlInsertDeleteCells .WebFormatting = xlWebFormattingNone .WebTables = """DGNav""" .Refresh BackgroundQuery:=False End With End Sub
I have done modification in web query but still, its not working, it download only current data, not the date entered in cell value i.e B5
I will greatly appreciate your any help on this
VBA Code:
Sub downloadnav()
Dim bmonth, bday, byear, emonth, eday, eyear As Integer Dim wst As Worksheet Dim TxtDate As String Const MyUrl As String = "http://www.idbifortis.com/TrackNAV.aspx?lm1=1"
In your code you are sending variables in the URL. You need to post them using the PostText command so that your code would look more like:
VBA Code:
Sub downloadnav() Dim wst As Worksheet Dim MyPost As String Const MyUrl As String = "http://www.idbifortis.com/TrackNAV.aspx?lm1=1"
MyPost = "TxtDate=04-Nov-2009" 'This is where you need to build your string from the date cell 'It may be better to format you cell as dd-mmm-yyy 'and then just take StrDate = Range("B5").Text 'then use MyPost = "TxtDate=" & strDate
'There are more parameters required when you look at the 'source HTML of the page in question:
MyPost = MyPost & "&CboFund=0&CboFund=0"
'Get the data '----------------------------------------------------------------------------------------------------- Sheets.Add ActiveSheet.Name = "temp" Sheets("temp").Select
'varconnection = MyUrl & TxtDate
With ActiveSheet.QueryTables.Add(Connection:= _ "URL;" & MyUrl, Destination:=Cells(1, 1)) .PostText = MyPost .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SaveData = True End With End Sub
The page also has a session variable that you may need to capture first and post back. To do this is a bit more complicated but it is detailed in this post.
Sorry to write back, I have go through many web pages to find the code similar to it or method but i didnt get any. Can you send me any link where i can learn this. i dont understand whts wrong in below mention code.
VBA Code:
Sub downloadnav() Dim wst As Worksheet Dim StrDate As String Dim MyPost As String Const MyUrl As String = "http://www.idbifortis.com/TrackNAV.aspx?lm1=1"
'MyPost = "TxtDate=04-Nov-2009" 'This is where you need to build your string from the date cell 'It may be better to format you cell as dd-mmm-yyy StrDate = Range("B5").Text MyPost = "TxtDate=" & StrDate 'There are more parameters required when you look at the 'source HTML of the page in question: MyPost = MyPost & "&CboFund=0&CboFund=0" Sheets.Add ActiveSheet.Name = "temp" Sheets("temp").Select 'varconnection = MyUrl & TxtDate
With ActiveSheet.QueryTables.Add(Connection:= _ "URL;" & MyUrl, Destination:=Cells(1, 1)) .PostText = MyPost .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SaveData = True End With End Sub
