Financial Calendar API

Overview

VBA (Visual Basic for Applications) macros allow for more sophisticated integration with the Financial Calendar API within Excel. You can create custom functions or subroutines to make API calls, process responses, and update your worksheets dynamically.

These examples use the MSXML2.XMLHTTP object to make HTTP requests. Ensure that "Microsoft XML, v6.0" (or similar) is enabled in your VBA project references (Tools > References).

Examples

VBA Function: Get Holiday Status

A custom VBA function to check if a specific date is a holiday:

Function IsHolidayAPI(targetDate As String, calendarName As String) As String
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    Dim url As String
    url = "https://fincalapi.com/v1/is_holiday?date=" & targetDate & "&calendar=" & calendarName & "&format=json"
    http.Open "GET", url, False
    http.Send
    IsHolidayAPI = http.responseText
End Function

' How to use in Excel: =IsHolidayAPI("2025-07-04", "SIFMA-US")
' Or in another VBA Sub: MsgBox IsHolidayAPI("2025-07-04", "SIFMA-US")

VBA Function: Get T+N Settlement Date

A custom VBA function to calculate T+N settlement date:

Function GetSettlementDateAPI(baseDate As String, calendarName As String, tPlusOffset As Integer) As String
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    Dim url As String
    url = "https://fincalapi.com/v1/settlement_date?date=" & baseDate & "&calendar=" & calendarName & "&tplus=" & tPlusOffset & "&format=json"
    http.Open "GET", url, False
    http.Send
    GetSettlementDateAPI = http.responseText
End Function

' How to use in Excel: =GetSettlementDateAPI("2025-07-03", "SIFMA-US", 2)
' Or in another VBA Sub: MsgBox GetSettlementDateAPI("2025-07-03", "SIFMA-US", 2)

VBA Subroutine: Fetch and Display Holidays (JSON)

A subroutine to fetch a list of holidays and display them in a message box. You can adapt this to write to a sheet.

Sub FetchHolidays()
    Dim http As Object
    Dim url As String
    Dim responseText As String

    Set http = CreateObject("MSXML2.XMLHTTP")
    url = "https://fincalapi.com/v1/holidays/range?calendar=NYSE&months_ahead=3&format=json"

    http.Open "GET", url, False
    http.Send

    responseText = http.responseText

    ' Basic check for valid JSON (requires JSON library for full parsing)
    If InStr(responseText, "[") > 0 And InStr(responseText, "]") > 0 Then
        MsgBox "Fetched Holidays (JSON):" & vbCrLf & responseText, vbInformation, "API Response"
    Else
        MsgBox "Error fetching holidays or invalid response: " & responseText, vbCritical, "API Error"
    End If

    Set http = Nothing
End Sub