Download intraday stock price automatically

[cds]

The following VBA code downloads intra day stock price for one stock/ticker at a time. To get the data for another stock/ticker I have to change ticker manually. How do I download the intra day stock price for many stocks in a list at one go and save the worksheet with stockname

Please guide me

Sub GetData()
    Dim ParameterSheet As Worksheet
    Dim DataSheet As Worksheet
    Dim ticker As String
    Dim exchange As String
    Dim interval As Integer
    Dim numPastTradingDays As Integer
    Dim qurl As String


    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual


    Set ParameterSheet = Sheets("Parameters")
    Set DataSheet = Sheets("Data")


    DataSheet.Cells.Clear
    ticker = ParameterSheet.Range("ticker").Value
    exchange = ParameterSheet.Range("exchange").Value
    interval = ParameterSheet.Range("interval").Value
    numPastTradingDays = ParameterSheet.Range("numTradingDays").Value


    qurl = "http://www.google.com/finance/getprices?" & _
           "q=" & ticker & _
           "&i=" & interval & _
           "&p=" & numPastTradingDays & "d" & _
           "&f=d,o,h,l,c,v"


QueryQuote:
    With DataSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("a1"))
        .BackgroundQuery = True
        .TablesOnlyFromHTML = False
        .Refresh BackgroundQuery:=False
        .SaveData = True
    End With


    DataSheet.Range("a1").CurrentRegion.TextToColumns Destination:=DataSheet.Range("a1"), DataType:=xlDelimited, _
                                                      TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                                                      Semicolon:=False, Comma:=True, Space:=False, other:=False


    DataSheet.Columns("A:G").ColumnWidth = 12


    '===Convert Google timestamp to Excel timestamp (only for Windows)
    Dim timeStamp As Double
    Dim timeStampRaw As String
    Dim timeZoneOffsetRaw As String
    Dim timeZoneOffset As Variant
    Dim numRows As Integer
    Dim i As Integer
    numRows = DataSheet.UsedRange.Rows.Count - 1


    timeZoneOffsetRaw = DataSheet.Range("a7")
    timeZoneOffset = (Mid(timeZoneOffsetRaw, InStr(timeZoneOffsetRaw, "=") + 1, 10))


    For i = 8 To numRows


        If Not IsNumeric(DataSheet.Range("a" & i)) Then


            timeStampRaw = DataSheet.Range("a" & i)
            timeStamp = (Mid(timeStampRaw, 2, Len(timeStampRaw) - 1))
            timeStamp = (timeStamp + timeZoneOffset * 60)
            DataSheet.Range("g" & i) = timeStamp / 86400 + 25569


        Else


            DataSheet.Range("g" & i).FormulaR1C1 = "=(RC[-6]*" & interval & "+" & timeStamp & ")/86400+25569"


        End If


    Next


    DataSheet.Range("g8:g" & numRows).NumberFormat = "d mmm yyyy h:mm;@"
    DataSheet.Range("G:G").Columns.AutoFit


    Application.Calculation = xlCalculationAutomatic


End Sub

 

[pedro-egoli]

Use Google Drive spreadsheet with the following formula in cell you require shareprice

=GOOGLEFINANCE(B2)

Where B2 contains "asx:agl" without the inverted commas.
This is for the australian stock exchange.

May not do all you require but at least it will get you the prices

Pedro