L
Legacy 266325
Guest
Hello
I am using the following code to import historical indices data for 5 different indexes in yahoo finance.
Public Sub Import_Yahoo_Finance_Historical()
Dim URL As String
Dim dateParams As String
'Date ranges from default earliest Yahoo start date (m/d/y) to current date (m/d/y)
dateParams = "&a=0&b=3&c=1977&d=" & Month(Date) - 1 & "&e=" & Day(Date) & "&f=" & Year(Date)
'Daily prices
URL = "http://ichart.finance.yahoo.com/table.csv?s=^GSPC" & "&g=d&ignore=.csv"
With Worksheets("FTSE100RAW")
With .QueryTables.Add(Connection:="TEXT;" & URL, Destination:=.Range("A1"))
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh BackgroundQuery:=False
End With
.QueryTables(1).Delete
End With
End Sub
This runs in the workbook VBA page and as noted runs for 5 different indexes whereby I simply change the s=^ to change the index name.
After searching for hours across the internet ,I cannot find a code that automates the refresh in the background as you would with a dynamic web query using the above URL. I know how to do this within Excel just using the URL ,however this doesn't work here as there are multiple pages to be imported, thus VBA is needed to add the string dimension.
I would like the workbook to update at four times in the day, or every 30 mins either is preferable.
Can anyone please help me, I have been searching for a while and have only found code that related to creating stock inoput tools within a module for individual stocks with specified date ranges.
Thank you in advance
Kate
I am using the following code to import historical indices data for 5 different indexes in yahoo finance.
Public Sub Import_Yahoo_Finance_Historical()
Dim URL As String
Dim dateParams As String
'Date ranges from default earliest Yahoo start date (m/d/y) to current date (m/d/y)
dateParams = "&a=0&b=3&c=1977&d=" & Month(Date) - 1 & "&e=" & Day(Date) & "&f=" & Year(Date)
'Daily prices
URL = "http://ichart.finance.yahoo.com/table.csv?s=^GSPC" & "&g=d&ignore=.csv"
With Worksheets("FTSE100RAW")
With .QueryTables.Add(Connection:="TEXT;" & URL, Destination:=.Range("A1"))
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh BackgroundQuery:=False
End With
.QueryTables(1).Delete
End With
End Sub
This runs in the workbook VBA page and as noted runs for 5 different indexes whereby I simply change the s=^ to change the index name.
After searching for hours across the internet ,I cannot find a code that automates the refresh in the background as you would with a dynamic web query using the above URL. I know how to do this within Excel just using the URL ,however this doesn't work here as there are multiple pages to be imported, thus VBA is needed to add the string dimension.
I would like the workbook to update at four times in the day, or every 30 mins either is preferable.
Can anyone please help me, I have been searching for a while and have only found code that related to creating stock inoput tools within a module for individual stocks with specified date ranges.
Thank you in advance
Kate
