optimal(xl)

VBA API EXAMPLE: RUN QUARTER-TO-DATE QUERIES

This macro sets two input cells to the start and end dates that represent a quarter-to-date period and then runs all Snowflake queries in the workbook that contains the macro.

This implies that one or more in-cell SQL statements in the workbook have a between constraint in their where clause that references these two input cells. For example:

2024-10-01	2024-12-31
select sym, sum(pnltot) from positions where dt between $start_date and $end_date group by sym

When would you use something like this? Like many VBA macros, this is just for convenience. If you, or the users of your model, are frequently entering the quarter-to-date period into the input cells and then refreshing data for this period, you can automate these steps by adding a 'Run for QTD' button atop your sheet which calls this macro.

Public Sub RefreshAllQuarterToDate()

    Dim qtr         As Long
    Dim tdy         As Date
    Dim qtrStart    As Date

    tdy = DateTime.Date

    Select Case DateTime.Weekday(tdy)
        Case vbSaturday
            tdy = tdy - 1
        Case vbSunday
            tdy = tdy - 2
    End Select

    qtr = Application.WorksheetFunction.Ceiling_Precise(DateTime.Month(tdy) / 3)
    qtrStart = DateTime.DateSerial(DateTime.Year(tdy), (qtr * 3) - 2, 1)

    With ThisWorkbook.Worksheets("query params")
        .Range("start_date").Value = qtrStart
        .Range("end_date").Value = DateTime.DateAdd("m", 3, qtrStart) - 1
    End With

    Application.Calculate

    OxlSnowflake.RunParallelAllInWorkbook ThisWorkbook

End Sub

Show full-screen with explanatory comments →