The class method of GetInfo extracts the info from each webpage using a css combination selector to target the page styling. Notes on GetInfo method and CSS selectors: Cells(.Rows.Count, columnNumber).End(xlUp).Row Public Function GetLastRow(ByVal ws As Worksheet, Optional ByVal columnNumber As Long = 1) As Long Cells(2, 1).Resize(1, UBound(headers) + 1) = headersįor i = LBound(results) To UBound(results) Results(ticker - 1) = http.GetInfo(html, endPoint) Set html = http.GetHTMLDoc("" & tickers(ticker, 1) & "/?p=" & tickers(ticker, 1)) ReDim results(0 To UBound(tickers, 1) - 1)įor ticker = LBound(tickers, 1) To UBound(tickers, 1) Set wsSource = ThisWorkbook.Worksheets("Sheet1") ' 3 In a standard module (module 1) Option Explicitĭim tickers(), ticker As Long, lastRow As Long, headers()ĭim wsSource As Worksheet, http As clsHTTP, html As HTMLDocument Set nodeList = html.querySelectorAll("tbody td") Public Function GetInfo(ByVal html As HTMLDocument, ByVal endPoint As Long) As Variantĭim nodeList As Object, i As Long, result(), counter As Long Public Function GetHTMLDoc(ByVal URL As String) As HTMLDocument Set http = CreateObject("MSXML2.XMLHTTP") So, in your project you add a class module called clsHTTP and put the following: For example, you might want to develop the class to handle server errors. The immediately obvious development, IMO, is you will want to add some error handling in. It is assumed your data is as shown, with header row being row 2. Using a class in this way means we save on the overhead of repeatedly creating and destroying the xmlhttp object and provides a nice descriptive set of exposed methods to handle the required tasks. One, GetHTMLDoc, to return the request response in an html document, and the other, GetInfo, to return an array of the items of interest from the page.
I use a bare bones class called clsHTTP to hold the XMLHTTP request object. However, I will show you a way using a class and a loop over URLs. With an API key, you can also potentially use the webservice function in Excel to retrieve and parse data.
#WEB SERVICE API VBA SERIES#
At the very bottom of this answer, I have a quick look at the time series functions available via the API. I believe Alpha Vantage now provide info the Yahoo Finance API used to*. You want to look into an API to provide this info if possible.
With much longer lists please see the ToDo section.
#WEB SERVICE API VBA CODE#
The code below works for the given test cases.
0 kommentar(er)
