Problems with conversion of CSV

[nsolling]

Hi,

Almost everything is working, for my bank import, but there is one thing I cannot make work.
When I have an amount being imported it is all good if it is a debit on the account, however the way the bank structures their CSV a credit-amount is in the same column as a debit, but postponed by a CR in the same column

As example the table structure of the CSV

DATE PAYEE CURRENCY AMOUNT (In Currency) Amount in local Currency
01/01/24. "DEBIT PAYEE NAME" USD 10 20
01/02/24. "Credit Payee Name" 10 Cr

When importing it is not clear to me how I can reflect if it is Credit or debit on the transaction

Any ideas?

[nielsmaerten]

Hi,

Looks like your bank is using a somewhat uncommon way of formatting debit/credit amounts.
No matter, ynab-buddy should be able to handle this using its Hooks feature (more info here).

Are you comfortable writing a bit of javascript code? I'll provide an example below, but you will likely need to adjust it before it works for you.

Here's how it works in general:

1. Get the hooks.js file

First you'll have to download the hooks.js file and add it ynab-buddy's config folder.
You can download the file here: https://github.com/nielsmaerten/ynab-buddy/blob/main/assets/config/hooks.js
Place it in the same folder as your config.yaml. (That's usually C:/Users/yourname/ynab-buddy on Windows or /Users/yourname/ynab-buddy on Mac)

2. Adjust the hooks file

The hook we're interested in is onRecord. This function will run for every record in your CSV file, and it's where we can make sure the amount is parsed correctly:

// {
//   Date: '1/1/2020',
//   Payee: 'Payee 1',
//   Amount: 10,
//   Memo: 'Memo 1',
//   Other_column: 'Other value',
// }
function onRecord(record) {
  // To skip the record, return null
  console.log(record);
  return record;
}

For your use-case, you might adjust onRecord to look like this:

function onRecord(record) {
  // Get the value of the AMOUNT column
  const columnTitle = 'AMOUNT'; // Make sure this matches the name of the column in your CSV file
  const amountRaw = record[columnTitle].toString() // We'll get its value as a string

  // For debugging purposes, log the amount we found to the console
  // This will appear in ynab-buddy's output
  console.log(columnTitle, ":", amountRaw);

  // To find out if the amount is credit or debit,
  // we'll check if the string contains "cr"
  const isCredit = amountRaw.toLowerCase().includes('cr');

  // Remove all non-numeric characters from the amount to get the actual value
  // Note: The code assumes that your CSV uses a period (`.`) as the decimal separator.
  // If your bank uses a comma (`,`), you'll need to adjust the `parseFloat` line accordingly to handle this difference.
  const amount = parseFloat(amountRaw.replace(/[^0-9.-]+/g,""));

  // If the transaction is debit, make the amount negative
  if (isCredit) {
    record[columnTitle] = amount;
  } else {
    record[columnTitle] = -amount;
  }

  // For debugging purposes, log the record to the console
  console.log("Changed", amountRaw, "to", amount);
  console.log(record);

  // Return the modified record
  return record;
}

Final Note

The code snippet above is a starting point and may not work perfectly without some adjustments.

If you encounter any issues or need help adjusting the script further, just let me know. 🙂
When you do, it's helpful if you can provide a few real lines from your CSV file as an example. (Just make sure to anonymize any sensitive information by changing amounts, names, and account numbers to random values.)

Good luck!