Excel stock data api vba
At one bank I did some work for, financial products were represented in JSON. However, JSON can also represent far more complex data structures. It’s a two-dimensional structure and very easy to ‘flatten’. The JSON example in this article is very simple. Now whether it’s a good approach to flatten JSON like that tool does, has a very succinct answer: it depends.
#EXCEL STOCK DATA API VBA CODE#
IN a sense, that’s sort of what we’re doing with the VBA code example in this article. The tool you provided the link for looks quite interesting.
#EXCEL STOCK DATA API VBA UPDATE#
I’ll probably update this post with your and his feedback as it’s been really helpful to know what things can go wrong. Let me know how you go with those 2 checks.Īnother commenter, Phil experienced issues as he’s running a 64-bit edition of Excel (I’m running a 32-bit edition). If you step through the code, you should be able to view the contents of the items object. If it looks okay, then check what’s in the “items” object. If there is no JSON or it looks corrupted, if you’re running this code from work (corporate) environment, make sure there are no firewall issues stopping you from retrieving data from the web. This will spit out the raw JSON to the Immediate window so you can check it. Try adding the following line right after the script.Eval line above: The first thing to check is the raw JSON that has been returned from the website. This takes the raw JSON which was returned from the website and turns it into an “object” which can be iterated through like a dictionary or collection. Set items = script.Eval(“(” & response & “)”) 'MsgBox Err.Number & " " & Err.Just prior to calling the GetItemCount helper function, there’s this line: 'MsgBox "Symbol (" & SYMBOL & ") not found.", vbCritical + vbOKOnly, "Symbol Not Found" Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ Sheets("BackTest").Columns("A:ah").AutoFit Semicolon:=False, Comma:=True, Space:=False, other:=False TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Sheets("BackTest").Range("a1").CurrentRegion.TextToColumns Destination:=Sheets("BackTest").Range("a1"), DataType:=xlDelimited, _ With (Connection:="URL " & qurl, Destination:=QuerySheet.Range("a2")) "&enddate=" & MonthName(Month(EndDate), True) & _ "+" & Day(startdate) & "+" & Year(startdate) & _ Qurl = qurl & "&startdate=" & MonthName(Month(startdate), True) & _ Startdate = SettingsSheet.Range("E6").ValueĮndDate = SettingsSheet.Range("E7").Value Worksheets("BackTest").Range("a:g").ClearContents ' Clear all cells in columns A:G formula rows from H5 through AF last row 'Application.Calculation = xlCalculationManual Symbol,timestamp,tradingDay,open,high,low,close,volume,openInterest input:Īpikey=&symbol=AAPL&type=daily&startDate=20100101&endDate=20130101&maxRecords=10&interval=60&order=asc&sessionFilter=EFK&splits=true÷nds=true&volume=sum&nearby=1&jerq=true&exchange=NYSE%2CAMEX%2CNASDAQ&backAdjust=false&daysToExpiration=1&contractRoll=expiration Sorry if the format did not come out correctly.