Just like the title says, I'm looking for a replacement source for stock info now that Yahoo has disabled the API many people have been using. The new source I've been looking at is found here: https://iextrading.com/developer/
My question is how to actually get the data in to Excel...I was thinking through VBA since that is what I had used to get the data from Yahoo. However, I think what I would like to do is well beyond my current abilities...I also tried using Excel's WEBSERVICE() function with the following URL to simply look at a price: https://api.iextrading.com/1.0/stock/aapl/price but that didn't work. From my understanding, IEX has made a plethora of data available to us for free, I just don't know how to access it. My reasoning for VBA is so that I am able to use an input list from a workbook for tickers, and would be able to put this data access in many workbooks. Any help is much appreciated. Additionally, any sort of direction as to where I can look to begin learning this on my own would be similarly welcome. Thanks.
Update: Code mentioned in my comment
Function StockPrice(ticker As String, item As String) As Double
Dim strURL As String, strCSV As Double, itemFound As Integer, tag As String
itemFound = 0
If item = "lastprice" Then
tag = "price"
itemFound = 1
ElseIf item = "pe" Then
tag = "peRatio"
itemFound = 1
End If
If itemFound = 1 Then
strURL = "https://api.iextrading.com/1.0/stock/" & ticker & "/" & tag
Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
XMLHTTP.Open "GET", strURL, False
XMLHTTP.send
StockPrice = XMLHTTP.responseText
Set XMLHTTP = Nothing
Else
StockPrice = "Item Not Found"
End If
End Function
I think I've mostly solved the issue. Here is the code for anyone who is interested. This works as a direct replacement for those using Yahoo Finance's API.
Function StockPrice(ticker As String, item As String)
Dim strURL As String, strCSV As Double, itemFound As Integer, tag As String
itemFound = 0
If item = "lastprice" Then
tag = "latestPrice"
itemFound = 1
ElseIf item = "pe" Then
tag = "peRatio"
itemFound = 1
ElseIf item = "company" Then
tag = "companyName"
itemFound = 1
ElseIf item = "sector" Then
tag = "sector"
itemFound = 1
ElseIf item = "open" Then
tag = "open"
itemFound = 1
ElseIf item = "yclose" Then
tag = "previousClose"
itemFound = 1
ElseIf item = "change" Then
tag = "change"
itemFound = 1
ElseIf item = "%change" Then
tag = "changePercent"
itemFound = 1
ElseIf item = "marketcap" Then
tag = "marketCap"
itemFound = 1
ElseIf item = "52high" Then
tag = "week52High"
itemFound = 1
ElseIf item = "52low" Then
tag = "week52Low"
itemFound = 1
End If
If itemFound = 1 Then
strURL = "https://api.iextrading.com/1.0/stock/" & ticker & "/quote/" & tag
Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
XMLHTTP.Open "GET", strURL, False
XMLHTTP.send
StockPrice = XMLHTTP.responseText
Set XMLHTTP = Nothing
Else
StockPrice = "Item Not Found"
End If
End Function
IEX has much more functionality than I've built here. Just not experienced enough to build around it. Check those features here: https://iextrading.com/developer/docs/