SHARE
TWEET

Untitled

a guest Jun 18th, 2019 41 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.         dateString = Format$(.Range("A1").Value, "yyyy-mm-dd")
  8.         lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
  9.         If lastRow >= 3 Then
  10.            .Range("K3:K" & lastRow).ClearContents
  11.             tickers = Application.Transpose(.Range("A3:A" & lastRow).Value)
  12.         Else
  13.            Exit Sub
  14.         End If
  15.     End With
  16.  
  17.     Dim s As String, re As Object, p As String, r As String, prices(), i As Long
  18.     ReDim prices(1 To UBound(tickers))
  19.  
  20.     p = """DATE_HERE"",""open"":([0-9.]+)"   'Format must be YYYY-MM-DD
  21.     p = Replace$(p, "DATE_HERE", dateString)
  22.     url = "https://cloud.iexapis.com/stable/stock/TICKER_HERE/chart/1m?token=pk_98e61bb72fd84b7d8b5f19c579fd0d9d"
  23.     Set re = CreateObject("VBScript.RegExp")
  24.  
  25.     With CreateObject("MSXML2.XMLHTTP")
  26.         For i = LBound(tickers) To UBound(tickers)
  27.             .Open "GET", Replace$(url, "TICKER_HERE", tickers(i)), False
  28.             .send
  29.             If .Status = 200 Then
  30.                 s = .responseText
  31.                 r = GetValue(re, s, p)
  32.             Else
  33.                 r = "Failed connection"
  34.             End If
  35.             prices(i) = r
  36.         Next
  37.     End With
  38.     ws.Cells(3, "K").Resize(UBound(prices), 1) = Application.Transpose(prices)
  39. End Sub
  40.  
  41. Public Function GetValue(ByVal re As Object, ByVal inputString As String, ByVal pattern As String) As String
  42.     With re
  43.         .Global = True
  44.         .pattern = pattern
  45.         If .test(inputString) Then  ' returns True if the regex pattern can be matched agaist the provided string
  46.             GetValue = .Execute(inputString)(0).submatches(0)
  47.         Else
  48.             GetValue = "Not found"
  49.         End If
  50.     End With
  51. 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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top