Skip to content

VBA

A function you can use in a worksheet or a macro: the date n business days after tradeDate in the given calendars.

Public Function SettlementDate(tradeDate As Date, n As Long, calendars As String) As Variant
Dim url As String
url = "https://api.financecalendarapi.com/v1/business-days/add" & _
"?date=" & Format(tradeDate, "yyyy-mm-dd") & "&n=" & n & _
"&calendars=" & calendars & "&format=text"
Dim http As Object
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", url, False
http.setRequestHeader "Authorization", "Bearer YOUR_KEY"
http.send
If http.Status = 200 Then
SettlementDate = CDate(http.responseText)
Else
SettlementDate = CVErr(xlErrValue) ' the response body says why
End If
End Function

In a cell: =SettlementDate(A2, 2, "USD,XNYS").

Errors come back as JSON problem details; read http.responseText to see the detail. Every request counts against your plan: for a long column, load a holiday list once (see Excel) and use WORKDAY.