SHARE
TWEET

Untitled

a guest Jun 18th, 2019 53 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Option Explicit
  2. Public Sub GetClosePrices()
  3.     Dim lastRow As Long, url As String, ws As Worksheet, tickers(), dateString As String
  4.  
  5.     Set ws = ThisWorkbook.Worksheets("Sheet1")
  6.     With ws
  7.         lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
  8.         If lastRow >= 3 Then
  9.            .Range("K3:K" & lastRow).ClearContents
  10.             tickers = Application.Transpose(.Range("A3:A" & lastRow).Value)
  11.         Else
  12.            Exit Sub
  13.         End If
  14.     End With
  15.  
  16.     Dim s As String, re As Object, p As String, r As String, prices(), i As Long
  17.     ReDim prices(1 To UBound(tickers))
  18.  
  19.     p = "open"":(.*?),"   'Format must be YYYY-MM-DD
  20.     url = "https://cloud.iexapis.com/stable/stock/TICKER_HERE/chart/1m?token=pk_98e61bb72fd84b7d8b5f19c579fd0d9d"
  21.     Set re = CreateObject("VBScript.RegExp")
  22.  
  23.     With CreateObject("MSXML2.XMLHTTP")
  24.         For i = LBound(tickers) To UBound(tickers)
  25.             .Open "GET", Replace$(url, "TICKER_HERE", tickers(i)), False
  26.             .send
  27.             If .Status = 200 Then
  28.                 s = .responseText
  29.                 r = GetValue(re, s, p)
  30.             Else
  31.                 r = "Failed connection"
  32.             End If
  33.             prices(i) = r
  34.         Next
  35.     End With
  36.     ws.Cells(3, "K").Resize(UBound(prices), 1) = Application.Transpose(prices)
  37. End Sub
  38.  
  39. Public Function GetValue(ByVal re As Object, ByVal inputString As String, ByVal pattern As String) As String
  40.     With re
  41.         .Global = True
  42.         .pattern = pattern
  43.         If .test(inputString) Then  ' returns True if the regex pattern can be matched agaist the provided string
  44.             GetValue = .Execute(inputString)(0).submatches(0)
  45.         Else
  46.             GetValue = "Not found"
  47.         End If
  48.     End With
  49. End Function
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top