Incorrect order of imported Account Balance for duplicated date

Support Issues
,
1

Hello,
I noticed an incorrect order of importing Account Values for entries with the same date – leading to wrong Quantity/Value of asset.

Here’s an example:
I’m trying to import a history of mortgage loan (kredyt hipoteczny, Pekao SA) in CSV format. Principal only based on remaining principal (Kapitał do spłaty) – as Pekao doesn’t export details about principal vs interest. Here are example values that appear to break the import:

Data księgowania;Typ operacji;Wartość;Kapitał do spłaty
20.06.2021;WYPŁATA KREDYTU;-12 000,00 PLN;146 842,50
20.06.2021;WYPŁATA KREDYTU;-128 000,00 PLN;134 842,50
13.06.2021;SPŁATA PROWIZJI/UBEZPIECZENIA;-1 109,90 PLN;6 842,50
13.06.2021;SPŁATA PROWIZJI/UBEZPIECZENIA;-2 357,60 PLN;5 732,60

Import preview looks good (exactly like data in CSV) but after import confirmation, the transaction log (in Transactions tab of the asset) shows:

Account Balance
13 cze 2021, 00:00
Quantity: 6842,5
Balance: 6842,5
Value: 6842,50

Account Balance
13 cze 2021, 00:00
Q: -1109,9
B: 5732,6
V: -1109,90

Account Balance
20 cze 2021, 00:00
Q: -12 000
B: 134 842,5
V: -12 000,00

Account Balance
20 cze 2021, 00:00
Q: 141 109,9
B: 146 842,5
V: 141 109,90

(Value of all entries is -1, as described in Tracking Debt | Capitally)

I can provide screenshots and import preset json but unfortunately I can’t attach any files, being a new user.

2

I think there are two issues here:

  1. Transactions are imported in the same order as they are seen in the CSV. In your case, the order is from the newest to the oldest and it actually should be the other way around. When setting up an import preset, you can enable the option to import the rows in the reverse order and it should fix the issue here.

CleanShot 2024-12-31 at 13.14.35@2x
CleanShot 2024-12-31 at 13.14.35@2x1834×674 64 KB

  1. Another thing is that you actually have two assets here. One is the remaining debt to be paid and the other is the insurance balance. I think you need to have two assets on a single account and track their balances.

And just to make sure - it’s the Price that should be -1 - the value should be left as is (which would be a negative quantity)

3

Hi,
Thanks for getting back.

Yes, sorry, my mistake. I meant Price not Value. It’s set in the asset to -1 and isn’t changed by the import.

  1. I forgot to mention that I tried to reverse the rows in the file manually but it didn’t help. I didn’t notice there’s a switch to reverse the rows in the UI. Unfortunately it didn’t help either.
  2. It’s a single asset. The insurance premium is added to the loan principal (the insurance premium payment is loaned from the bank and added to the debt). Anyway, you can ignore Typ operacji column and focus on Kapitał do spłaty. After the import the Quantity shows invalid values and the Balance seems to be off by one row.

It seems that for duplicated dates, the import uses a fixed and implicit sort order which doesn’t depend on the row order.

4

Can you send me these invalid values you see for the above rows?

In general, in the Transactions table, Quantity column will show the difference created by this Balance Update transaction, while Balance column should equal the Quantity field in the transaction

6

Actually, I tested it again and importing with reverse order creates correct transaction entries.

Still what’s confusing is that transactions with the same date always appear in the same order, no matter if sorted by date asc or desc.

Transactions sorted asc (all good):

Account Balance
13 cze 2021, 00:00
Quantity: 5732,60
Balance: 5732,60
Value: 5732,60

Account Balance
13 cze 2021, 00:00
Q: 1109,9
B: 6842,50
V: 1109,90

Account Balance
20 cze 2021, 00:00
Q: 128000
B: 134842,5
V: 128000,00

Account Balance
20 cze 2021, 00:00
Q: 12000
B: 146 842,5
V: 12000

Transactions sorted desc (confusing because reading from the bottom the Balance is first higher 6842,5; then after adding more quantity it goes lower, 5732,6)

Account Balance
20 cze 2021, 00:00
Q: 128000
B: 134842,5
V: 128000,00

Account Balance
20 cze 2021, 00:00
Q: 12000
B: 146 842,5
V: 12000,00

Account Balance
13 cze 2021, 00:00
Quantity: 5732,6
Balance: 5732,6
Value: 5732,60

Account Balance
13 cze 2021, 00:00
Q: 1109,9
B: 6842,5
V: 1109,90

My workaround for now is to manually sort the transactions explicitly by modifying the transaction time (e.g. 00:00 to 00:01 for the later transactions). Maybe some import order should be kept internally, to allow proper sorting when displaying the entries?

BTW, shouldn’t Value be negative, with positive Quantity and negative Price e.g.
Quantity: 1109,9
Price: -1
Value: -1109,9? (currently it shows positive 1109,9)
Or is the Value always an absolute value?

Bonus nitpick about formatting: Quantity and Balance shows with zero or single decimal places, while Price and Value are formatted with two decimal places.

7

It’s because the insertion order is used if the sorting value is equal. But probably the insertion order should be sorted in the same direction so it’s not confusing.

Transaction value is the cash flow. So a regular buy is negative and sell is positive. If the price is negative, it will be reversed.

When you have a balance transaction, it creates a buy/sell transaction underneath.

It should be less confusing with the official handling of liabilities.

It’s because a typical quantity and balance is in whole shares, not currency. There’s a plan to improve formatting by making it configurable

8

A better fix, though still manual at your end:
Add a column that increments by 1 and then sort by that column in descending order, then delete the column, save and reimport.