Module: MercuryBanking::Reports::Reconciliation

Defined in:
lib/mercury_banking/reports/reconciliation.rb

Overview

Module for reconciliation between ledger and Mercury balances

Instance Method Summary collapse

Instance Method Details

#generate_reconciliation_report(client, account_name, start_date, end_date = nil, format = 'ledger') ⇒ Object

Generate a reconciliation report to identify when balances diverged



8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
 
# File 'lib/mercury_banking/reports/reconciliation.rb', line 8

def generate_reconciliation_report(client, , start_date, end_date = nil, format = 'ledger')
  puts "Generating reconciliation report for #{} from #{start_date} to #{end_date || 'present'}..."

  # Find the account by name
   = (client, )
  return unless 

  # Get current balance from Mercury
  current_mercury_balance = ['currentBalance']

  # Get all transactions for the account
  transactions = (client, ['id'], start_date)

  # Filter by end date if specified
  if end_date
    end_date_obj = Date.parse(end_date)
    transactions = transactions.select do |t|
      transaction_date = Date.parse(t["postedAt"] || t["createdAt"])
      transaction_date <= end_date_obj
    end
  end

  if transactions.empty?
    puts "No transactions found for reconciliation."
    return
  end

  # Sort transactions by date
  transactions = sort_transactions_by_date(transactions)

  # Group transactions by month for easier analysis
  monthly_transactions = group_transactions_by_month(transactions)

  # Create a temporary file for ledger export
  require 'tempfile'
  temp_file = Tempfile.new(['mercury_reconciliation', ".#{format}"])
  output_file = temp_file.path

  # Export all transactions to the ledger file
  case format
  when 'ledger'
    export_to_ledger(transactions, output_file, [], false)
  when 'beancount'
    export_to_beancount(transactions, output_file, [], false)
  else
    puts "Unsupported format: #{format}. Please use 'ledger' or 'beancount'."
    temp_file.unlink
    return
  end

  # Analyze each month to find when divergence occurred
  puts "\n=== Monthly Reconciliation Analysis ==="
  puts "#{'Period'.ljust(15)}#{'Mercury Balance'.ljust(20)}#{'Ledger Balance'.ljust(20)}#{'Difference'.ljust(15)}Status"
  puts "-" * 80

  # Initialize variables for tracking
  divergence_point = nil
  monthly_data = []

  monthly_transactions.each do |month, month_transactions|
    # Calculate the Mercury balance at the end of this month
    last_transaction = month_transactions.last
    last_date = Date.parse(last_transaction["postedAt"] || last_transaction["createdAt"])

    # Get ledger balance for this month
    month_end_date = last_date.strftime("%Y-%m-%d")
    ledger_balance = get_ledger_balance_at_date(output_file, month_end_date, , format)

    # Calculate Mercury balance at this point in time
    # This is an approximation based on current balance and subsequent transactions
    mercury_balance_at_month_end = calculate_mercury_balance_at_date(current_mercury_balance, transactions,
                                                                     last_date)

    # Calculate difference
    difference = mercury_balance_at_month_end - ledger_balance

    # Determine status
    status = difference.abs < 0.01 ? "✓ Balanced" : "⚠️ Diverged"

    # Record the first divergence point
    divergence_point = month if difference.abs >= 0.01 && divergence_point.nil?

    # Format for display
    month_str = month
    mercury_balance_str = format("$%.2f", mercury_balance_at_month_end)
    ledger_balance_str = format("$%.2f", ledger_balance)
    difference_str = format("$%.2f", difference)

    puts month_str.ljust(15) + mercury_balance_str.ljust(20) + ledger_balance_str.ljust(20) + difference_str.ljust(15) + status

    monthly_data << {
      period: month,
      mercury_balance: mercury_balance_at_month_end,
      ledger_balance: ledger_balance,
      difference: difference,
      status: status
    }
  end

  puts "-" * 80

  # If divergence was found, analyze the specific month in more detail
  if divergence_point
    puts "\n=== Detailed Analysis of Divergence Point: #{divergence_point} ==="

    # Get transactions for the divergence month
    divergence_month_transactions = monthly_transactions[divergence_point]

    # Check for pending or failed transactions
    pending_transactions = divergence_month_transactions.reject { |t| t["status"] == "posted" }

    if pending_transactions.any?
      puts "\nPotential issues found: #{pending_transactions.count} transactions with non-posted status"
      puts "\nTransactions with non-posted status:"

      pending_transactions.each do |t|
        date = t["postedAt"] ? Time.parse(t["postedAt"]).strftime("%Y-%m-%d") : Time.parse(t["createdAt"]).strftime("%Y-%m-%d")
        amount = format("$%.2f", t["amount"])
        description = t["bankDescription"] || t["externalMemo"] || "Unknown transaction"
        status = t["status"]

        puts "#{date} | #{amount} | #{status.upcase} | #{description}"
      end
    end

    # Check for transactions on the same day with opposite amounts (potential duplicates or corrections)
    potential_corrections = find_potential_corrections(divergence_month_transactions)

    if potential_corrections.any?
      puts "\nPotential corrections or duplicates found:"

      potential_corrections.each do |pair|
        t1, t2 = pair
        date1 = t1["postedAt"] ? Time.parse(t1["postedAt"]).strftime("%Y-%m-%d") : Time.parse(t1["createdAt"]).strftime("%Y-%m-%d")
        date2 = t2["postedAt"] ? Time.parse(t2["postedAt"]).strftime("%Y-%m-%d") : Time.parse(t2["createdAt"]).strftime("%Y-%m-%d")

        puts "#{date1} | #{format('$%.2f',
                                  t1['amount'])} | #{t1['bankDescription'] || t1['externalMemo'] || 'Unknown'}"
        puts "#{date2} | #{format('$%.2f',
                                  t2['amount'])} | #{t2['bankDescription'] || t2['externalMemo'] || 'Unknown'}"
        puts "-" * 50
      end
    end
  end

  # Clean up temporary file
  temp_file.unlink

  monthly_data
end