Simplest alternative to CSV for a developer to produce?

Dear community,

Since importing transactions from CSV files are kind of limited, what would be the simplest / easiest format to grasp for a software developer?

I can only get CSV files, and I can create a script that converts it to the desirable format. What would be the format with the simplest structure, so that it is easy to understand and build?

Many thanks,
Anton

If you have programming skills and intend to import data from another program to Manager, use batch update (batch create to determine the format) as described here

The official position is, don’t do it, retain your old system for old data, for new data set a start date and starting balances.

Why you may want to do it anyway is discussed here

1 Like

Thanks for your quick response.

What about OFX, will I be able to produce 3+ split transactions, including multi-currency ones, via OFX?

The problem I’m trying to solve is that I have NO accounting system in place, and I want to start being more organized.

I used to send bank statements, receipts, and manually written notes / lists to my accountant, but I cannot analyze my cashflow and, most importantly, calculate currency exchange gain and loss, both realized and unrealized.

I can download CSV only from a few financial institutions that I use. They all produce CSV files of no particular standard. So the approach here would be to program a script that produces some standard file.

Therefore my question consists of two parts:

  1. What would be the format that is the simplest to learn and produce programmatically?

  2. What would be the format that would allow me multi-currency 3+ split transactions?

Oops
Looks like I didn’t read your post carefully enough
To import bank transactions see this guide
Cash and Bank Transactions: Import bank statements

And yes ofx is supported
csv is supported but the least reliable because it is the least standardized

To understand multi currencny importing you must first be familiar how multi currency is manually entered in Manager, see these guides
Initial Setup: Set base currency | Define foreign currencies
And
Cash and Bank Accounts: Use multiple currencies

Yes, thanks, familiarized myself with those already.

So, just to make sure, it will be possible to import multi-currency 3+split transactions from OFX and there are no known issues with it at the moment?

Disclosure: I’ve been trying Gnucash for a few days since that’s what I found first - it does have a sophisticated CSV import, but there are known bugs when it comes to importing multi-currency transactions from CSV. More to that, Gnucash cannot currently report unrealized currency exchange gain/loss for when my foreign currency comes as an income and not as a result of buying it / exchanging with my local currency.

Bank import will record movement of money between the specified bank account and a single Manager account as defined in each bank rule. A user can edit the transaction manually during import to do more elaborate allocation if required.

Gains and losses due to exchange rate variations are recorded by manually entering the exchange rate in settings.

Movement between accounts is done by inter account transfers, see this guide
Inter Account Transfers: Transfer money between bank and cash accounts

Where the accounts are in different currencies, the paid and received amount are separately entered allowing recording the implied exchange rate.

If you wanted to enter payment of an invoice amount in a currency foreign to the received bank accounts currency you would need to enter two transactions

  • Payment of the foreign amount in to an account denominated in that currency so achieving full payment of the invoice

  • inter account transfer from foreign denominated account to local denominated account.

Doing so automatically in Manger would require batch importing and external code

Is it possible to import multicurrency transactions through QIF files?

I know they have no mention of currency. But what if I have two files - one generated from the US Bank statement, the second one from the AUD Bank statement. After importing both separately, is there a way for me to somehow “connect” the corresponding records from the two files, as well as assign the exchange rate explicitly?

You are missing an important point, @antonphp. An imported bank statement, by definition, involves only one currency, because the bank account is in only one currency. There is no such thing as a multi-currency bank transaction; there are only deposits and withdrawals in the currency of the bank account.

The same is true of manually entered receipts and payments. Each is in a specific currency according to the bank or cash account it is received in or paid from.

The postings to various accounts in the chart of accounts to which receipts, payments, deposits, or withdrawals are allocated may have designated equivalent amounts in other currencies, but the movements of money themselves can only be in a single currency.

By a “multi-currency transaction” I mean this:

2020-01-01 "Exchange"
    Assets:MyBank:Checking_USD  -100.00 USD @ 1.5 AUD
    Assets:MyBank:Checking_AUD   150.00 AUD

I realize Checking_USD and Checking_AUD are two separate checking accounts, each using a particular currency.

What I’m trying to achieve is, after importing from 2 CSV files, for Manager to understand that both postings / legs / splits belong to the same transaction, i.e. the one named “Exchange” in my example above.

Does my question make sense this way?

That is an inter account transfer
See these guides

Given the simultaneous currency conversion, I would use Managers batch create with external automated creation of the cvs file.

The problem is that these are not the same transaction. As @Patch wrote while I was composing this answer, what you describe is an inter account transfer in Manager. But inter account transfers are really just a shortcut method for entering simultaneous withdrawals and deposits in two separate cash or bank accounts without needing a clearing account. In other words, one transfer is two separate receipts or payments.

I see. So, if there is no association between the two legs of that transaction, it means I can separately import two separate statements one after another. Now, if there is no association between the “withdrawal” and “deposit” legs of the transaction, will Manager be able to:

  1. display both within a single transaction in a journal
    and
  2. calculate realized P&L correctly (the features that, as it has been said, is coming)

By this, I mean sometihng like:

2020-01-01 "Exchange"
    Assets:MyBank:Checking_USD  -100.00 USD @ 1.5 AUD
    Assets:MyBank:Checking_AUD   150.00 AUD

Command-line text-input-based ledgers somehow do it, so I was hoping this was the case for Manager as well?

Please enable the “Inter account transfer” tab in manager and try using it.

No, because they are not journal entries, but receipts and payments into and out of bank accounts.

Hey team, no updates to this process since us discussing it last time?
I’m still a freelance developer, still dealing with clients from all around the world, still experiencing all the same problems with currency conversion and P&L calculation, and still no tool exist to process it smoothly.

As said you should try batch create. I was able to automate everything, even very complex tasks. But you should know Excel very well…

Have you looked? Inter account transfers allow you to specify both legs of the transfer in different currencies according to the denominations of the bank or cash accounts involved. But the program will not recognize withdrawals and deposits from different statement imports as being related. See the example below: