Public Sub getUBOTupdateByAC()
    Dim sid, sessionId, acctNo, beginDate, endDate
    Dim lastDateRow As Long, totalRow As Long
    Dim dataLastRow As Long, nextDataRow As Long
    Dim existingRow As Long
    Dim checkingExisting As Boolean
    Dim requestBody As String
    Dim enteredEndDate As String
    sid = "F160043296A"
    acctNo = Trim$(ActiveSheet.Name)

    'The sheet name must be a 12-digit account number.
    If Len(acctNo) <> 12 Or acctNo Like "*[!0-9]*" Then
        MsgBox "The sheet name must contain exactly 12 numbers.", vbExclamation
        Exit Sub
    End If

    sessionId = getToken

    'Use the last transaction date in column A. If there are no transaction
    'rows yet, start from August 1, 2026.
    lastDateRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
    If lastDateRow < 2 Or Len(Trim$(CStr(ActiveSheet.Cells(lastDateRow, 1).Value))) = 0 Then
        lastDateRow = 1
        beginDate = DateSerial(2026, 8, 1)
    ElseIf Not TryGetTransactionDate(ActiveSheet.Cells(lastDateRow, 1).Value, beginDate) Then
        MsgBox "The last transaction date in column A is not a valid date.", vbExclamation
        Exit Sub
    End If
    
    endDate = DateSerial(Year(beginDate), Month(beginDate) + 1, 0)

    'If the last transaction is already on the month's final day,
    'extend the query through the end of the following month.
    If DateValue(beginDate) = endDate Then
        endDate = DateSerial(Year(beginDate), Month(beginDate) + 2, 0)
    End If

    enteredEndDate = InputBox( _
        Prompt:="Confirm or change the query end date (yyyy-mm-dd):", _
        Title:="Query End Date", _
        Default:=Format$(endDate, "yyyy-mm-dd"))

    If Len(Trim$(enteredEndDate)) = 0 Then Exit Sub

    If Not TryGetTransactionDate(enteredEndDate, endDate) Then
        MsgBox "The query end date is not valid. Use yyyy-mm-dd.", vbExclamation
        Exit Sub
    End If

    If endDate < beginDate Then
        MsgBox "The query end date cannot be earlier than the start date.", vbExclamation
        Exit Sub
    End If
    
    requestBody = "{    ""sid"": """ & sid & """,    ""sessionId"": """ & sessionId & """,    ""acctNo"": """ & acctNo & """,    ""beginDate"": """ & Format$(beginDate, "yyyymmdd") & """,    ""endDate"": """ & Format$(endDate, "yyyymmdd") & """ }"
    Debug.Print requestBody
   
    ' Send request
    Dim HTTPReq As New MSXML2.XMLHTTP60
    HTTPReq.Open "POST", "https://www.ubot.com.tw/MyBank/IBKB010102", False
    HTTPReq.setRequestHeader "Content-Type", "application/json"
    HTTPReq.send requestBody
    ' Get response
    Dim response As String
    response = HTTPReq.responseText
    Debug.Print response
    
    Dim JSON As Object, i As Long
    Dim NTDetailsList As Object
    Dim Item As Variant
    
    Set JSON = ParseJson(response)
    
    ' *** Headers ***'
    Dim headers() As String, Header As String, index As Integer
    headers = Split("Transaction Date,Transaction Time,Account Date,Summary,Payment,Deposit,Account Balance,PS,Memo,AutoNote,Note,Mahakala,Ajam,Aba,Ani,Prayer,Teacher,Translator,other,Center,Total", ",")
    For i = LBound(headers) To UBound(headers)
        Header = headers(i)
        index = i + 1
        Debug.Print index, Header
        ActiveSheet.Cells(1, index).Value = Header
        If Header = "Memo" Then ActiveSheet.Columns(index).NumberFormat = "@"
    Next i
  
    '*** check for data ***'
    If IsEmpty(JSON("RespBody")("NTDetailList")) = True Then
        MsgBox "No Data"
        Exit Sub
    End If
    
    '***clear the old total row and append after the last dated row***
    totalRow = lastDateRow + 1
    For i = 5 To 21
        ActiveSheet.Cells(totalRow, i).Value = ""
        ActiveSheet.Cells(totalRow, i).Font.Bold = False
    Next
    dataLastRow = lastDateRow
    nextDataRow = lastDateRow + 1
    checkingExisting = True

    '*** fill values 1-22 ***'
    For Each Item In JSON("RespBody")("NTDetailList")
        'Only check the overlapping records at the start of the response.
        'After the first new record, append everything else without checking.
        If checkingExisting Then
            existingRow = FindTransactionRow(Item("TraDate"), _
                                              Item("TraTime"), _
                                              Item("Balance"), _
                                              dataLastRow)
            If existingRow > 0 Then GoTo SkipTransaction
            checkingExisting = False
        End If

        i = nextDataRow
        nextDataRow = nextDataRow + 1
        dataLastRow = i

        ActiveSheet.Cells(i, 1).Value = Item("TraDate")
        ActiveSheet.Cells(i, 2).Value = Item("TraTime")
        ActiveSheet.Cells(i, 3).Value = Item("AccountDate")
        ActiveSheet.Cells(i, 4).Value = Item("Summary")
        ActiveSheet.Cells(i, 5).Value = Item("Expenditure")
        
        '*** Deposit 6 ***'
        'fill value
            ActiveSheet.Cells(i, 6).Value = Item("Income")
        'if Deposit = Total
            ActiveSheet.Cells(i, 6).FormatConditions.Delete
            ActiveSheet.Cells(i, 6).FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=$U" & i
        'then format
            ActiveSheet.Cells(i, 6).FormatConditions(ActiveSheet.Cells(i, 6).FormatConditions.Count).SetFirstPriority
            With ActiveSheet.Cells(i, 6).FormatConditions(1).Font
                .Color = -16752384
                .TintAndShade = 0
            End With
            With ActiveSheet.Cells(i, 6).FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 13561798
                .TintAndShade = 0
            End With
            ActiveSheet.Cells(i, 6).FormatConditions(1).StopIfTrue = False
        'end format
        
        ActiveSheet.Cells(i, 7).Value = Item("Balance")
        ActiveSheet.Cells(i, 8).Value = Item("PS")
        ActiveSheet.Cells(i, 9).Value = Item("TraSum")
        ActiveSheet.Cells(i, 10).Value = getNameOfAccountNo("Accounts", "A:B", Item("TraSum"))
        
        '*** 11-20 *************************
        
        ActiveSheet.Cells(i, 21).Value = "=SUM(INDIRECT(""L"" & ROW()):INDIRECT(""T"" & ROW()))"
        'if Total = Deposit
            ActiveSheet.Cells(i, 21).FormatConditions.Delete
            ActiveSheet.Cells(i, 21).FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=$F" & i
        'then
            ActiveSheet.Cells(i, 21).FormatConditions(ActiveSheet.Cells(i, 21).FormatConditions.Count).SetFirstPriority
            With ActiveSheet.Cells(i, 21).FormatConditions(1).Font
                .Color = -16752384
                .TintAndShade = 0
            End With
            With ActiveSheet.Cells(i, 21).FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 13561798
                .TintAndShade = 0
            End With
            ActiveSheet.Cells(i, 21).FormatConditions(1).StopIfTrue = False
        'end format
SkipTransaction:
    Next

    i = nextDataRow 'totals row
    
    '**** add total ***'
    ActiveSheet.Cells(i, 5).Value = "=SUM(E2:E" & (i - 1) & ")"
    ActiveSheet.Cells(i, 5).Font.Bold = True
    ActiveSheet.Cells(i, 6).Value = "=SUM(F2:F" & (i - 1) & ")"
    ActiveSheet.Cells(i, 6).Font.Bold = True
    
    ActiveSheet.Cells(i, 12).Value = "=SUM(L2:L" & (i - 1) & ")"
    ActiveSheet.Cells(i, 12).Font.Bold = True
    
    ActiveSheet.Cells(i, 13).Value = "=SUM(M2:M" & (i - 1) & ")"
    ActiveSheet.Cells(i, 13).Font.Bold = True
    ActiveSheet.Cells(i, 14).Value = "=SUM(N2:N" & (i - 1) & ")"
    ActiveSheet.Cells(i, 14).Font.Bold = True
    ActiveSheet.Cells(i, 15).Value = "=SUM(O2:O" & (i - 1) & ")"
    ActiveSheet.Cells(i, 15).Font.Bold = True
    ActiveSheet.Cells(i, 16).Value = "=SUM(P2:P" & (i - 1) & ")"
    ActiveSheet.Cells(i, 16).Font.Bold = True
    ActiveSheet.Cells(i, 17).Value = "=SUM(Q2:Q" & (i - 1) & ")"
    ActiveSheet.Cells(i, 17).Font.Bold = True
    ActiveSheet.Cells(i, 18).Value = "=SUM(R2:R" & (i - 1) & ")"
    ActiveSheet.Cells(i, 18).Font.Bold = True
    ActiveSheet.Cells(i, 19).Value = "=SUM(S2:S" & (i - 1) & ")"
    ActiveSheet.Cells(i, 19).Font.Bold = True
    ActiveSheet.Cells(i, 20).Value = "=SUM(T2:T" & (i - 1) & ")"
    ActiveSheet.Cells(i, 20).Font.Bold = True
    ActiveSheet.Cells(i, 21).Value = "=SUM(U2:U" & (i - 1) & ")"
    ActiveSheet.Cells(i, 21).Font.Bold = True
    
    ActiveWorkbook.Save
    MsgBox "Complete"
    
End Sub

Private Function FindTransactionRow(ByVal transactionDate As Variant, _
                                    ByVal transactionTime As Variant, _
                                    ByVal transactionBalance As Variant, _
                                    ByVal dataLastRow As Long) As Long
    Dim rowNumber As Long
    Dim wantedDate As String, wantedTime As String, wantedBalance As String
    Dim storedDate As String, storedTime As String, storedBalance As String
    Dim displayedDate As String, displayedTime As String, displayedBalance As String
    Dim worksheetLastRow As Long

    wantedDate = TransactionDateKey(transactionDate)
    wantedTime = TransactionTimeKey(transactionTime)
    wantedBalance = TransactionBalanceKey(transactionBalance)

    'Always search every dated row currently on the sheet.
    worksheetLastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
    If worksheetLastRow > dataLastRow Then dataLastRow = worksheetLastRow

    For rowNumber = 2 To dataLastRow
        storedDate = TransactionDateKey(ActiveSheet.Cells(rowNumber, 1).Value2)
        storedTime = TransactionTimeKey(ActiveSheet.Cells(rowNumber, 2).Value2)
        storedBalance = TransactionBalanceKey(ActiveSheet.Cells(rowNumber, 7).Value2)

        'The displayed-text fallback handles cells whose underlying values
        'were converted differently by Excel when they were first imported.
        displayedDate = DigitsOnly(ActiveSheet.Cells(rowNumber, 1).Text)
        displayedTime = DigitsOnly(ActiveSheet.Cells(rowNumber, 2).Text)
        displayedBalance = TransactionBalanceKey(ActiveSheet.Cells(rowNumber, 7).Text)

        If (storedDate = wantedDate Or displayedDate = wantedDate) _
           And (storedTime = wantedTime Or displayedTime = wantedTime) _
           And (storedBalance = wantedBalance Or displayedBalance = wantedBalance) Then
            Debug.Print "Skipping existing transaction at row"; rowNumber; _
                        wantedDate; wantedTime; wantedBalance
            FindTransactionRow = rowNumber
            Exit Function
        End If
    Next rowNumber

    Debug.Print "First new transaction:"; wantedDate; wantedTime; wantedBalance
End Function

Private Function TransactionDateKey(ByVal cellValue As Variant) As String
    Dim parsedDate As Variant
    Dim digits As String

    If TryGetTransactionDate(cellValue, parsedDate) Then
        TransactionDateKey = Format$(parsedDate, "yyyymmdd")
    Else
        digits = DigitsOnly(Trim$(CStr(cellValue)))
        If Len(digits) >= 8 Then
            TransactionDateKey = Left$(digits, 8)
        Else
            TransactionDateKey = digits
        End If
    End If
End Function

Private Function TransactionTimeKey(ByVal cellValue As Variant) As String
    Dim timeText As String, digits As String
    Dim fractionPosition As Long

    If IsDate(cellValue) Then
        TransactionTimeKey = Format$(CDate(cellValue), "hhmmss")
    Else
        timeText = Trim$(CStr(cellValue))

        'Ignore fractional seconds, for example 11:15:24.000.
        fractionPosition = InStrRev(timeText, ".")
        If InStr(timeText, ":") > 0 And fractionPosition > InStrRev(timeText, ":") Then
            timeText = Left$(timeText, fractionPosition - 1)
        End If

        digits = DigitsOnly(timeText)
        If Len(digits) > 6 Then digits = Right$(digits, 6)
        TransactionTimeKey = Right$("000000" & digits, 6)
    End If
End Function

Private Function TransactionBalanceKey(ByVal cellValue As Variant) As String
    Dim balanceText As String

    balanceText = Replace(Trim$(CStr(cellValue)), ",", "")
    If Len(balanceText) = 0 Then Exit Function

    If IsNumeric(balanceText) Then
        TransactionBalanceKey = CStr(CDec(balanceText))
    Else
        TransactionBalanceKey = balanceText
    End If
End Function

Private Function DigitsOnly(ByVal textValue As String) As String
    Dim characterIndex As Long, oneCharacter As String

    For characterIndex = 1 To Len(textValue)
        oneCharacter = Mid$(textValue, characterIndex, 1)
        If oneCharacter Like "#" Then DigitsOnly = DigitsOnly & oneCharacter
    Next characterIndex
End Function

Private Function TryGetTransactionDate(ByVal cellValue As Variant, ByRef transactionDate As Variant) As Boolean
    Dim dateText As String

    If IsDate(cellValue) Then
        transactionDate = CDate(cellValue)
        TryGetTransactionDate = True
        Exit Function
    End If

    dateText = DigitsOnly(Trim$(CStr(cellValue)))
    If Len(dateText) <> 8 Or Not dateText Like "########" Then Exit Function

    On Error GoTo InvalidDate
    transactionDate = DateSerial(CInt(Left$(dateText, 4)), _
                                 CInt(Mid$(dateText, 5, 2)), _
                                 CInt(Right$(dateText, 2)))
    If Format$(transactionDate, "yyyymmdd") <> dateText Then Exit Function

    TryGetTransactionDate = True
InvalidDate:
End Function