Module: MercuryBanking::Formatters::ExportFormatter

Included in:
CLI::Financials::FinancialsCommand, CLI::Main
Defined in:
lib/mercury_banking/formatters/export_formatter.rb

Overview

Formatter for exporting transactions to different formats

Instance Method Summary collapse

Instance Method Details

#export_to_beancount(transactions, output_file, reconciled_transactions = [], verbose = false) ⇒ Object

Export transactions to beancount format



147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
 
# File 'lib/mercury_banking/formatters/export_formatter.rb', line 147

def export_to_beancount(transactions, output_file, reconciled_transactions = [], verbose = false)
  File.open(output_file, 'w') do |file|
    file.puts "; Mercury Bank Transactions Export"
    file.puts "; Generated on #{Time.now.strftime('%Y-%m-%d %H:%M:%S')}"
    file.puts

    # Filter out failed transactions
    valid_transactions = transactions.reject { |t| t["status"] == "failed" }

    # Get unique account names for debug output
    if verbose
      # First get all account names
       = valid_transactions.map do |t|
        t["accountName"] || "Mercury"
      end
      # Then clean and make unique
      accounts = .map { |name| name.gsub(/[^a-zA-Z0-9]/, '-') }.uniq
      puts "Creating beancount file with the following accounts:"
      accounts.each do ||
        puts "- Assets:#{}"
      end
    end

    valid_transactions.each do |t|
      date = t["postedAt"] ? Time.parse(t["postedAt"]).strftime("%Y-%m-%d") : Time.parse(t["createdAt"]).strftime("%Y-%m-%d")
      description = t["bankDescription"] || t["externalMemo"] || "Unknown transaction"
      amount = t["amount"].to_f
       = t["accountName"] || "Mercury"
       = .gsub(/[^a-zA-Z0-9]/, '-')
      transaction_id = t["id"]

      # Check if this transaction is reconciled
      is_reconciled = reconciled_transactions.include?(transaction_id)
      flag = is_reconciled ? "*" : "!"

      # Sanitize description for beancount format
      description = description.gsub(/[\r\n]+/, ' ').gsub(/"/, '\\"')

      file.puts "#{date} #{flag} \"#{description}\""
      file.puts "  ; Transaction ID: #{transaction_id}"
      file.puts "  ; Status: #{t['status']}"

      # Add reconciliation metadata
      if is_reconciled
        file.puts "  reconciled: \"true\""
        file.puts "  reconciled_at: \"#{Time.now.strftime('%Y-%m-%d')}\""
      else
        file.puts "  reconciled: \"false\""
        file.puts "  reconciled_at: \"\""
      end

      if amount.negative?
        file.puts "  Expenses:Unknown  #{format('%.2f', amount.abs)} USD"
        file.puts "  Assets:#{}  #{format('%.2f', amount)} USD"
      else
        file.puts "  Assets:#{}  #{format('%.2f', amount)} USD"
        file.puts "  Income:Unknown  #{format('%.2f', -amount)} USD"
      end
      file.puts
    end
  end
end

#export_to_csv(transactions, output_file, reconciled_transactions = [], verbose = false) ⇒ Object

Export transactions to CSV format



269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
 
# File 'lib/mercury_banking/formatters/export_formatter.rb', line 269

def export_to_csv(transactions, output_file, reconciled_transactions = [], verbose = false)
  require 'csv'

  CSV.open(output_file, 'w') do |csv|
    # Write header
    csv << ['Date', 'Description', 'Amount', 'Account', 'Transaction ID', 'Status', 'Reconciled', 'Reconciled At']

    # Filter out failed transactions
    valid_transactions = transactions.reject { |t| t["status"] == "failed" }

    if verbose
      puts "Creating CSV file with #{valid_transactions.size} transactions"
      puts "CSV columns: Date, Description, Amount, Account, Transaction ID, Status, Reconciled, Reconciled At"
    end

    # Write transactions
    valid_transactions.each do |t|
      date = t["postedAt"] ? Time.parse(t["postedAt"]).strftime("%Y-%m-%d") : Time.parse(t["createdAt"]).strftime("%Y-%m-%d")
      description = t["bankDescription"] || t["externalMemo"] || "Unknown transaction"

      # Replace newlines with spaces for CSV format
      description = description.gsub(/[\r\n]+/, ' ')

      amount = t["amount"].to_f
       = t["accountName"] || "Mercury Account"
      transaction_id = t["id"]
      status = t["status"]

      # Check if this transaction is reconciled
      is_reconciled = reconciled_transactions.include?(transaction_id)
      reconciled = is_reconciled ? "Yes" : "No"
      reconciled_at = is_reconciled ? Time.now.strftime("%Y-%m-%d") : ""

      csv << [date, description, amount, , transaction_id, status, reconciled, reconciled_at]
    end
  end
end

#export_to_hledger(transactions, output_file, reconciled_transactions = [], verbose = false) ⇒ Object

Export transactions to hledger format



211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
 
# File 'lib/mercury_banking/formatters/export_formatter.rb', line 211

def export_to_hledger(transactions, output_file, reconciled_transactions = [], verbose = false)
  File.open(output_file, 'w') do |file|
    file.puts "; Mercury Bank Transactions Export"
    file.puts "; Generated on #{Time.now.strftime('%Y-%m-%d %H:%M:%S')}"
    file.puts

    # Filter out failed transactions
    valid_transactions = transactions.reject { |t| t["status"] == "failed" }

    # Get unique account names for debug output
    if verbose
      accounts = valid_transactions.map { |t| t["accountName"] || "Mercury Account" }.uniq
      puts "Creating hledger file with the following accounts:"
      accounts.each do ||
        puts "- Assets:#{}"
      end
    end

    valid_transactions.each do |t|
      date = t["postedAt"] ? Time.parse(t["postedAt"]).strftime("%Y-%m-%d") : Time.parse(t["createdAt"]).strftime("%Y-%m-%d")
      description = t["bankDescription"] || t["externalMemo"] || "Unknown transaction"
      amount = t["amount"].to_f
       = t["accountName"] || "Mercury Account"
      transaction_id = t["id"]

      # Check if this transaction is reconciled
      is_reconciled = reconciled_transactions.include?(transaction_id)
      status_marker = is_reconciled ? " * " : " ! "

      # Sanitize description for hledger format
      description = description.gsub(/[\r\n]+/, ' ').gsub(/;/, '\\;')

      file.puts "#{date}#{status_marker}#{description}"
      file.puts "    ; Transaction ID: #{transaction_id}"
      file.puts "    ; Status: #{t['status']}"

      # Add reconciliation metadata
      if is_reconciled
        file.puts "    reconciled: true"
        file.puts "    reconciled-at: #{Time.now.strftime('%Y-%m-%d')}"
      else
        file.puts "    reconciled: false"
        file.puts "    reconciled-at:"
      end

      if amount.negative?
        file.puts "    Expenses:Unknown  $#{format('%.2f', amount.abs)}"
        file.puts "    Assets:#{}  $#{format('%.2f', amount)}"
      else
        file.puts "    Assets:#{}  $#{format('%.2f', amount)}"
        file.puts "    Income:Unknown  $#{format('%.2f', -amount)}"
      end
      file.puts
    end
  end
end

#export_to_ledger(transactions, output_file, reconciled_transactions = [], verbose = false) ⇒ Object

Export transactions to ledger format



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
 
# File 'lib/mercury_banking/formatters/export_formatter.rb', line 8

def export_to_ledger(transactions, output_file, reconciled_transactions = [], verbose = false)
  File.open(output_file, 'w') do |file|
    file.puts "; Mercury Bank Transactions Export"
    file.puts "; Generated on #{Time.now.strftime('%Y-%m-%d %H:%M:%S')}"
    file.puts

    # Add account declarations to ensure ledger recognizes them
    accounts = transactions.map { |t| t["accountName"] || "Mercury Account" }.uniq
    accounts.each do ||
      file.puts "account Assets:#{}"
    end
    file.puts "account Expenses:Unknown"
    file.puts "account Income:Unknown"
    file.puts

    # Debug: Show what accounts are being created
    if verbose
      puts "Creating ledger file with the following accounts:"
      accounts.each do ||
        puts "- Assets:#{}"
      end
    end

    # Filter out failed transactions
    valid_transactions = transactions.reject { |t| t["status"] == "failed" }

    # Sort transactions chronologically by timestamp
    sorted_transactions = sort_transactions_chronologically(valid_transactions)

    sorted_transactions.each do |t|
      # Get the full timestamp for precise ordering
      timestamp = t["postedAt"] || t["createdAt"]
      date = Time.parse(timestamp).strftime("%Y/%m/%d")
      time = Time.parse(timestamp).strftime("%H:%M:%S")
      description = t["bankDescription"] || t["externalMemo"] || "Unknown transaction"
      amount = t["amount"].to_f
       = t["accountName"] || "Mercury Account"
      transaction_id = t["id"]

      # Check if this transaction is reconciled
      is_reconciled = reconciled_transactions.include?(transaction_id)

      # Sanitize description for ledger format - replace newlines with spaces and escape semicolons
      description = description.gsub(/[\r\n]+/, ' ').gsub(/;/, '\\;')

      # Ensure the description doesn't start with characters that could be interpreted as a date
      # or other ledger syntax elements
      if description =~ /^\d/ || description =~ /^[v#]/ || description =~ /^\s*\d{4}/ || description =~ %r{^\s*\d{2}/\d{2}}
        description = "- #{description}"
      end

      # Format the transaction with proper indentation and alignment
      file.puts "#{date} #{is_reconciled ? '* ' : ''}#{description}"
      file.puts "    ; Transaction ID: #{transaction_id}"
      file.puts "    ; Timestamp: #{timestamp}"
      file.puts "    ; Time: #{time}"
      file.puts "    ; Status: #{t['status']}"

      # Add reconciliation metadata
      file.puts "    ; :reconciled: true" if is_reconciled

      # Add counterparty information if available
      if t["counterpartyName"]
        # Sanitize counterparty name
        counterparty = t["counterpartyName"].gsub(/[\r\n]+/, ' ').gsub(/;/, '\\;')
        file.puts "    ; Counterparty: #{counterparty}"
      end

      # Add additional metadata if available
      if t["externalMemo"] && t["externalMemo"] != description
        # Sanitize memo
        memo = t["externalMemo"].gsub(/[\r\n]+/, ' ').gsub(/;/, '\\;')
        file.puts "    ; Memo: #{memo}"
      end

      # Add the postings
      file.puts "    Assets:#{}  $#{format('%.2f', amount)}"
      if amount.positive?
        # Credit (money coming in)
        file.puts "    Income:Unknown  $#{format('%.2f', -amount)}"
      else
        # Debit (money going out)
        file.puts "    Expenses:Unknown  $#{format('%.2f', amount.abs)}"
      end
      file.puts
    end
  end

  puts "Verifying ledger file validity..."
  verify_ledger_file(output_file, verbose)
end

#sort_transactions_chronologically(transactions) ⇒ Object

Sort transactions chronologically by timestamp



101
102
103
104
105
106
107
 
# File 'lib/mercury_banking/formatters/export_formatter.rb', line 101

def sort_transactions_chronologically(transactions)
  transactions.sort_by do |t|
    # Use postedAt if available, otherwise fall back to createdAt
    timestamp = t["postedAt"] || t["createdAt"]
    Time.parse(timestamp)
  end
end

#verify_ledger_file(output_file, verbose = false) ⇒ Object

Verify that the ledger file is valid



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
 
# File 'lib/mercury_banking/formatters/export_formatter.rb', line 110

def verify_ledger_file(output_file, verbose = false)
  # Debug: Verify the ledger file is valid
  cmd = "ledger -f #{output_file} balance"
  output = `#{cmd}`

  if $?.success?
    if verbose
      puts "Ledger verification output: #{output}"

      # Show all accounts in the ledger file
      cmd = "ledger -f #{output_file} accounts"
      output = `#{cmd}`

      if $?.success?
        puts "All accounts in the ledger file:"
        puts output
      else
        puts "Warning: Could not list accounts in the ledger file."
        puts "This may indicate a problem with the file format, but the balance command succeeded."
      end
    end
  else
    puts "Warning: Ledger verification failed. The file may contain formatting issues."
    puts "Error output: #{output}"

    # Try to identify problematic lines
    cmd = "grep -n '^[0-9]' #{output_file} | head -10"
    problematic_lines = `#{cmd}`
    puts "First few transaction lines for inspection:"
    puts problematic_lines
  end
rescue StandardError => e
  puts "Error during ledger verification: #{e.message}"
  puts "This doesn't necessarily mean the file is invalid, but there may be issues with the ledger command."
end