Reply to topic

Closing a brokerage account

Section_8
Registered Member
Posts
4
Karma
0

Closing a brokerage account

Thu Mar 28, 2019 3:20 am
Hello,
I am running kmymoney 5.0.3 on linux.

I am unable to close several old brokerage accounts left over from investment accounts that have been closed. In all cases, the balance is 0.00, but the message when mousing over the "Close account" button is "The balance of the account must be zero before the account can be closed."

My guess is that this is a round off problem, and the balances are close to, but not exactly, zero. In one case, the balance of 0.00 is red, and after adding 0.01, the balance stays 0.00, but is now black.

Is there a way to clear these balances to zero so they can be closed, or is something else wrong?
User avatar ipwizard
KDE Developer
Posts
673
Karma
3
OS

Re: Closing a brokerage account

Thu Mar 28, 2019 6:45 am
Ooops, there is a problem, I guess. Your investigation with the one cent addition shows the problem. We need to check where this can come from (as it should not).


ipwizard, proud to be a member of the KMyMoney forum since its beginning. :-D
openSuSE Leap 15.0 64bit, KF5
Section_8
Registered Member
Posts
4
Karma
0

Re: Closing a brokerage account

Thu Mar 28, 2019 4:47 pm
I assumed the round off is because the investments in these accounts are mutual funds, and the transactions all involve fractional prices and share quantities, usually 3 or 4 digit precision.
User avatar ipwizard
KDE Developer
Posts
673
Karma
3
OS

Re: Closing a brokerage account

Thu Mar 28, 2019 5:55 pm
The following little perl script might help us to extract your data. Here it is:
Code: Select all
#!/usr/bin/perl
#
# Extract split field values for a specific account and field into a CSV file
#
# The script reads a plain text KMyMoney XML data file on stdin and writes
# the following columns as CSV format on stdout:
#
# transactionId;splitId;<selected-field>[;numeric amount]
#
# in case the selected field is either 'shares' or 'value' the colum 'amount'
# will be added to the output which contains the value in a form that can be
# processed by spreadsheet programs
#
#
# usage: zcat data.kmy | getsplitpart.pl --acc=A000002 --field=shares > output.csv
#
# (C) 2019 by Thomas Baumgart <tbaumgart@kde.org>


my @args = @ARGV;
my $accountid;
my $field;
my $fields = ";shares;value;payee;";
my @files = ();

while ($#args >= 0) {
    my $a = shift @args;
    $accountid = $1 if ($a =~ /--acc=(.*)/);
    $field = $1 if ($a =~ /--field=(.*)/);
}

die ("Field '$field' not supported.") if ($fields !~ /\;$field\;/);
die ("Missing account id. Use --acc= to specify.") if ($accountid eq "");
die ("Missing field name. Use --field= to specifiy.") if ($field eq "");

my $transactionid;
my $splitid;

# print header line
print "transactionId;splitId;$field";
print ";amount" if ($field =~ /^(shares|value)$/);
print "\n";

while(<STDIN>) {
    if ($_ =~ /<SCHEDULED_TX .+=/) {
        $_ =~ /id="([^"]*)"/;
        $scheduleid = $1;
        next;
    }
    if ($_ =~ /<TRANSACTION [a-z]+=/) {
        $_ =~ /id="([^"]*)"/;
        $transactionid = $1;
        $transactionid = $scheduleid if($transactionid eq "");
        next;
    }
    if ($_ =~ /<SPLIT [a-z]+=/) {
        $_ =~ /id="([^"]*)"/;
        $splitid = $1;
        if ($_ =~ /account="$accountid"/) {
            if ($_ =~ /$field="([^"]*)"/) {
                my $value = $1;
                if ($value =~ /-?[0-9]+\/[0-9]+/) {
                    $result = eval $value;
                    if ($@) {
                        print "Invalid string: '$value'\n";
                    } else {
                        print "$transactionid;$splitid;$value;$result\n";
                    }
                } else {
                    print "$transactionid;$splitid;$value\n";
                }
            }
        }
    }
}
The balance of an account is calculated by summing up the shares of each split, which you can do e.g. using your favorite spreadsheet application once the data is extracted from the kmy file. Here's how you would do that:

  1. Determine the account id of the account in question
  2. Use the above script to convert the data into a CSV file
  3. Load the CSV file into the spreadsheet application for further inspection
The first step can easily be done by using the following command
zgrep '<ACCOUNT ' data.kmy | grep -i '<text>'

Replace <text> with whatever is a unique part of your account name and use the name of your file instead of data.kmy. This will provide some output and you are interested in the
id="A......"

part. The following example operates on a file named tus.kmy and looks for fraspa case insensitive.
Code: Select all
thb@thb-nb:~$ zgrep '<ACCOUNT ' tus.kmy | grep -i 'fraspa'
  <ACCOUNT parentaccount="AStd::Asset" description="" name="1100 Giro Fraspa" lastmodified="2019-03-20" type="1" opened="2016-01-01" lastreconciled="2016-07-16" number="" currency="EUR" id="A000009" institution="I000002">

In this example, the account id is A000009 which we need as argument to the script. The call to the script would then be (we have made it executable before)
zcat tus.kmy | ./getsplitpart.pl --acc=A000009 --field=shares > output.csv

which yields the following data in output.csv
transactionId;splitId;shares;amount
T000000000000000008;S0001;23469/100;234.69
T000000000000000029;S0001;-357/5;-71.4
T000000000000000065;S0001;287/2;143.5
:
:

Now it's a matter of finding the problem which I would leave to you. ;)


ipwizard, proud to be a member of the KMyMoney forum since its beginning. :-D
openSuSE Leap 15.0 64bit, KF5
Section_8
Registered Member
Posts
4
Karma
0

Re: Closing a brokerage account

Sun Mar 31, 2019 12:27 am
ipwizard,
Thank you - using your perl script, I was able to track down the problem transactions and close these accounts.
In these brokerage accounts, there were a few old transactions like this:
Code: Select all
transactionId;splitId;shares;amount
T000000000000000101;;-327635186037/50000000;-6552.70372074
T000000000000000102;;-22552628823/12500000;-1804.21030584

The transaction amounts, with precision > 2 digits were causing the problem, and were several years old, probably entered in kmymoney 4.7.1 or so. I made sure the price entry setting for these accounts was "Total for all shares", and just edited those transactions to round the amounts down to 0.01 to fix them so the displayed balance is precisely equal to the internal balance, and I could then clear and close them.

Thank you very much
tomruthchew
Registered Member
Posts
7
Karma
0

Re: Closing a brokerage account

Tue Apr 30, 2019 5:09 am
Hello. I am trying to close an investment account that has a value of -$2500 but I cannot close it. The value is wrong but I am not sure why as it should be zero. The security that is associated with it has 0 shares in it,so I can close it but I am not able to close the actual investment account because I assume the value is not zero. What do I need to do to make the value of the investment account to zero so I can close it? Thanks.

 
Reply to topic

Bookmarks



Who is online

Registered users: Baidu [Spider], Bing [Bot], boudewijn, djarvie, duns, Exabot [Bot], Google [Bot], jackjo, Kawaii Specter, keyths, peje, polsson, rafamar, Sogou [Bot], surt, tymond