Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Option Explicit
- Public Sub GetClosePrices()
- Dim lastRow As Long, url As String, ws As Worksheet, tickers(), dateString As String
- Set ws = ThisWorkbook.Worksheets("Sheet1")
- With ws
- lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
- If lastRow >= 3 Then
- .Range("K3:K" & lastRow).ClearContents
- tickers = Application.Transpose(.Range("A3:A" & lastRow).Value)
- Else
- Exit Sub
- End If
- End With
- Dim s As String, re As Object, p As String, r As String, prices(), i As Long
- ReDim prices(1 To UBound(tickers))
- p = "latestPrice"":(.*?)," 'Format must be YYYY-MM-DD
- url = "https://cloud.iexapis.com/stable/stock/TICKER_HERE/quote?token=pk_98e61bb72fd84b7d8b5f19c579fd0d9d"
- Set re = CreateObject("VBScript.RegExp")
- With CreateObject("MSXML2.XMLHTTP")
- For i = LBound(tickers) To UBound(tickers)
- .Open "GET", Replace$(url, "TICKER_HERE", tickers(i)), False
- .send
- If .Status = 200 Then
- s = .responseText
- r = GetValue(re, s, p)
- Else
- r = "Failed connection"
- End If
- prices(i) = r
- s = vbNullString
- Next
- End With
- ws.Cells(3, "K").Resize(UBound(prices), 1) = Application.Transpose(prices)
- End Sub
- Public Function GetValue(ByVal re As Object, ByVal inputString As String, ByVal pattern As String) As String
- With re
- .Global = True
- .pattern = pattern
- If .test(inputString) Then ' returns True if the regex pattern can be matched agaist the provided string
- GetValue = .Execute(inputString)(0).submatches(0)
- Else
- GetValue = "Not found"
- End If
- End With
- End Function
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement