[GNC] Splitting a multi-year file

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

[GNC] Splitting a multi-year file

Andrea Borgia
Hi.

For the past decade, I've had only a few gnucash files, not one per year:
basically, I'd create a new one when the file would be large enough at the
end of the year to notice that startup time.

If I wanted to retroactively split them by single year as I started doing
now, how could I do that? Is there a smarter way to do it other than
treating it as just another XML file and editing it manually?

Of course, I am aware that I would have to create a few transactions for
end of year closing and new year opening.

Regards,
Andrea.
_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] Splitting a multi-year file

Christian Kluge
Hi Andrea,

Am 03.03.2019 um 19:19 schrieb Andrea Borgia:

> Hi.
>
> For the past decade, I've had only a few gnucash files, not one per year:
> basically, I'd create a new one when the file would be large enough at the
> end of the year to notice that startup time.
>
> If I wanted to retroactively split them by single year as I started doing
> now, how could I do that? Is there a smarter way to do it other than
> treating it as just another XML file and editing it manually?
>
> Of course, I am aware that I would have to create a few transactions for
> end of year closing and new year opening.
>
> Regards,
> Andrea.

I’d suggest making balance sheets for each year to exported.
Afterwards I would use File\Export\Transactions to CSV.
There I would select the appropriate time range.
I also would export the account structure to CSV.
In the one-year-only file I would import the account structure and  make
entries for the opening balances.
Lastly I would import the transactions from the CSV files.

Kind regards

Christian Kluge

_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] Splitting a multi-year file

Andrea Borgia
Il 03/03/19 20:29, Christian Kluge ha scritto:

> I’d suggest making balance sheets for each year to exported.
> Afterwards I would use File\Export\Transactions to CSV.
> There I would select the appropriate time range.
> I also would export the account structure to CSV.
> In the one-year-only file I would import the account structure and  make
> entries for the opening balances.
> Lastly I would import the transactions from the CSV files.

Christian, thanks for the detailed explanation.

So far, these are the problems I had:

1) the account selection is a bit of a chore. I have a chart of accounts
which is fairly extensive for a private person (111 accounts) and I
cannot select the top-level accounts and have all subaccounts included,
apparently: when I do that, the top-level accounts are selected plus the
second level accounts of the last top level I clicked. If I start
manually exploding all parts of the account tree and then hitting
ctrl-a, then I get an increasing number of selected accounts, meaning
that "select subaccounts" does not work on currently selected accounts,
just on the last one. Is that to be expected?

2) importing the CSV for the accounts shows an empty preview and I
cannot move past this screen. If I check the file, it looks like a
regular CSV to me and I haven't changed the export defaults. Also, I
have tried adjusting the number of header lines but no luck. Maybe I
have to use "export accounts" instead of "export account tree to csv"?

3) during import, I need to select "gnucash export settings" first, then
I have to match each account from the CSV to the corresponding account
in the tree. There is no default mapping, which is weird considering the
accounts obviously have the same names since they come from the same
source. Is that normal?

Thanks,
Andrea.
_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] Splitting a multi-year file

David Cousens
Andrea

To export the account tree use File->Export->Export Accounts to export the
account structure into a new book file directly. It works fine with GnuCash
V3.4. What version of GnuCash are you using and on what operating system?
Using the export of the account Tree to CSV will also work but the direct
export into a new file is simpler.

You then need to export the transactions you want to transfer to this book
to a CSV file. File->Export->Export transactions to CSV.  Don't select the
simple layout option. Select all accounts you wish to export from and then
set the date range you wish to export and create the export file.

To import , open GnuCash in the new book file. File->Import->Import
Transactions from CSV. Select the file you have just exported. Select the
GnuCash Export Format. Make sure Multisplit is selected. If the data line
comes up as pink (or other colour) this is a warning that a format doesn't
match usually the data format. Set the date format to whatever is used in
your locale (or whatever format has been exported) and ensure that Skip 1
leading line is selected.  You will then see the data headings in the first
import record have a line through them and the pink or other color shading
in the data lines should clear. The column headers should correspnd with the
headers in the first skipped lines.

In the next window you need to map the accounts specified in the file to be
imported onto the accounts in the account tree that you imported. As yet
there is no matching procedure so it is laborious but only has to be done
for those accounts that transactions being imported will have splits to.  In
your case the mapped accounts should be the same as the imported account
names when you have finished.

In the next window (matcher) hopefully all the transactions to be imported
will turn up with a green background. Correct any that don't have accounts
correctly assigned and assigned and then import the transactions.

At present the import is not working correctly when the transactions involve
accounts with multiple currencies and I suspect trading accounts although I
have not checked these yet. You will have to edit any transactions between
accounts in different currencies. At present the import appears to create a
spurious split ot splits for an unrlated amount and appears to create an
additional transaction in each non-book currency. Hopefully you don't have
multicurrency transactions.

As Christian recommended it is probably a good idea to create balance sheet
reports however that may be of limited usefulness as the Balance sheet at a
given date will not be the same in the new file unless you have copied all
records up to that date and as you are splitting up into annual books that
will not be the case. More useful may be Income Statements (also contains
expenses)for each period from the original book. that should then agree with
an Income Statement created for the same period from the new book

The other point is as you do this you will also have to carry forward
closing balances from the new book from the previous year you imported  into
the new book for the  year you are currently importing as Opening Balances
in that new book

David Cousens




-----
David Cousens
--
Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-User-f1415819.html
_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
David Cousens
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] Splitting a multi-year file

Andrea Borgia
Hello, David.


Thanks for the explanation. Rest of the comments are inline.



> To export the account tree use File->Export->Export Accounts to export the
> account structure into a new book file directly. It works fine with GnuCash
> V3.4. What version of GnuCash are you using and on what operating system?
> Using the export of the account Tree to CSV will also work but the direct
> export into a new file is simpler.

Yup, thanks for confirming. I'm using v3.4 on Debian, "testing" branch.


> To import , open GnuCash in the new book file. File->Import->Import
> Transactions from CSV. Select the file you have just exported. Select the
> GnuCash Export Format. Make sure Multisplit is selected. If the data line
> comes up as pink (or other colour) this is a warning that a format doesn't
> match usually the data format. Set the date format to whatever is used in
> your locale (or whatever format has been exported) and ensure that Skip 1
> leading line is selected.  You will then see the data headings in the first
> import record have a line through them and the pink or other color shading
> in the data lines should clear. The column headers should correspnd with the
> headers in the first skipped lines.

I used the "Gnucash Export format" in the "Load settings" and all lines
are now white with no "stop" sign on the left side, after changing the
date to match. Now I understand what was wrong in my earlier attempt.


> In the next window you need to map the accounts specified in the file to be
> imported onto the accounts in the account tree that you imported. As yet
> there is no matching procedure so it is laborious but only has to be done
> for those accounts that transactions being imported will have splits to.  In
> your case the mapped accounts should be the same as the imported account
> names when you have finished.

Automatching would be a great addition, surely, but at least I got
through to saving the imported entries.


> At present the import is not working correctly when the transactions involve
> accounts with multiple currencies and I suspect trading accounts although I
> have not checked these yet. You will have to edit any transactions between
> accounts in different currencies. At present the import appears to create a
> spurious split ot splits for an unrlated amount and appears to create an
> additional transaction in each non-book currency. Hopefully you don't have
> multicurrency transactions.

Hmm, I do have some old shares in investment funds and also have used
multiple currencies over the years (EUR, ITL, GBP, FIM, EEK, NOK and I'm
probably forgetting a couple). I used intermediate accounts to handle
the money exchange then created separate income & expenses accounts for
each currency as appropriate. Should I keep an eye on those? Or perhaps
you are talking about something even fancier than I have in my books.


> As Christian recommended it is probably a good idea to create balance sheet
> reports however that may be of limited usefulness as the Balance sheet at a
> given date will not be the same in the new file unless you have copied all
> records up to that date and as you are splitting up into annual books that
> will not be the case. More useful may be Income Statements (also contains
> expenses)for each period from the original book. that should then agree with
> an Income Statement created for the same period from the new book

I think I'm going to stick with the balance sheet, since I already have
the closing balances even if I did not create a new file each year. The
income statement for 2015, for example, is mostly zeros except for a
single mistake which I just discovered (bank interests for 2015 were
lumped together with 2016, oh well...)



Thanks again,
Andrea.


_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.