calc and bookkeeping

users
#1

First post, not sure it is the right place, hi there!

For 2015 I would like to keep a shadow bookkeeping in Calc in order to check our bookkeeping program for a while.

Input: In order to keep things simple: three columns with Description, Amount and a LedgerCode.

Output: Sums per LedgerCode

Several solutions are thinkable of, such as conditional testing and lookups. However, if the list grows and contains thousands of lines it will influence performance.

The question: What is the most efficient approach in order to keep the sheet alive after adding record 2384?

Example:
LedgerCode: 1, 2, 3

#2

Rather than use Calc, I recommend using Base with a simple database.
You will need one table (table1) containing four fields: Description, Amount, LedgerCode, and PK (primary key)
      Then you will need a query to obtain your results. the SQL for the query is

Select SUM("Amount"), "LedgerCode" FROM table1 GROUP BY "LedgerCode" ORDER BY "LedgerCode"

      I could create this and send it to you off list if you want.

Dan

#3

Hi :slight_smile:
Spreadsheets are not really made to be good at accounting and bookkeeping.
GnuCash or other dedicated programs might be better. Spreadsheets are
designed to do a lot of calculations.

So, spreadsheets ARE great for doing "working papers" such as to;
* "reconciliations" (although the dedicated bookkeeping programs would
still probably be better) usually "bank reconciliations" although there
might be other types of accounts that need reconciling against each other
to check both sets of records are in sync and correctly acknowledge
outstanding items.
* VAT or tax calculations
* preparation for "End of Year" accounts and /or management accounts

Generally in bookkeeping if you get near the end of a page, or at a
convenient point (say at the end of a month) then you do all the subtotals
as "brought forwards" (seems like the wrong term but it's the one used in
bookkeeping and accountancy) often shortened to "b/f" or "b/fwds". Then at
the top of the new page/sheet you have those same figures copied in as
"carried forwards" or "c/f" or "c/fwds". In spreadsheets those carried
forwards values can be set as formulas so that if the b/fwds figures change
then those changes ripple through the whole set of accounts. That makes it
easier for bookkkeepers but it's a nightmare for accountants.

It sounds like you are trying to set-up 2 new systems to cross-check
against each other. This sounds like a complete nightmare and likely to
create tons of trouble. Using spreadsheets for accounting or bookkeeping
takes a lot of time and resources to set-up. If you really need to run 2
different systems at the same time then it's probably better to just keep
the old system going rather than to pour resources into setting up a 2nd
different one that you also can't be completely confident about!

Also it's a good idea to get a specialist in to help. An experienced
bookkeeper might be able to set-up something workable in spreadsheets much
more quickly than someone who is not trained in accounts. There are a LOT
of potential pitfalls they are probably used to dodging (although i know of
a few they usually miss).
Regards from
Tom :slight_smile:

#4

Hi,

Input: In order to keep things simple: three columns with
Description, Amount and a LedgerCode.

Output: Sums per LedgerCode

PivotTable aka DataPilot is exaktly what you are looking for. It´s a
feature of Calc, of course. :wink:

Here is the guide:
https://wiki.documentfoundation.org/images/6/69/CG4108-PivotTables.pdf

For better understanding I recommend reading "Example 1" on pages 5
through 12 of this document, first:

https://wiki.openoffice.org/w/images/5/52/0308CG3-DataPilot.pdf

Stefan

#5

Spreadsheets are not really made to be good at accounting and
bookkeeping. GnuCash or other dedicated programs might be better.
Spreadsheets are designed to do a lot of calculations.

GnuCash... haven't used it for so long that I forgot it did exist. Nice suggestion, already installed it for experimenting.

I am a Calc abuser. For example, I made a tailored invoice system because there was nothing on the market for my specific needs. It runs for 10 years now, contains many thousands of records in text tables and never disappointed me. I know it shouldn't be done in Calc, it is grown over the years with the intention to make it a database after testing - it never happened.

It sounds like you are trying to set-up 2 new systems to cross-check
against each other. This sounds like a complete nightmare and likely to
create tons of trouble.

You're probably right. The current solution offers me in time or cloud accounting or getting a SQL server. None is an option. So GnuCash for 2015 or a year later.

Thanks for your comprehensive answer.

#6

Hi :slight_smile:
Ahh, just had a quick look at a set of accounts and noticed that b/fwd and
c/fwd ARE used the logical way around. For some reason i've always thought
they were counter.

So the c/fwds figures ARE at the bottom of the sheet and show the
sub-totals of that sheet. The b/fwds figures ARE at the top of the new
sheets.

Phew!
Regards from
Tom :slight_smile:

#7

Together with Stefan's solution - could even be combined - I am grateful. So for now GnuCash. Thanks, I am going to experiment with both suggestions for educational purposes. Still missing an answer in a sense of straight formulas in Calc for a relative simple problem if we skip the amount of lines that could make it complex.

#8

Found it, Cell A3 contains:

=DSUM($B5:$C12;"Amount";A1:A2)

Sleeping better now, itch is gone, hope screen dump arrives too. And again, thanks for all suggestions and great alternatives.

#9

Apparently my screen dump vaporized, the data:

    A B C
1 Code Code Code
2 1 2 3
3 101 65 22
4
5 Descr Amount Code
6 booking 1 EUR 100,00 1
7 booking 2 EUR 50,00 2
8 booking 3 EUR 20,00 3
9 booking 4 EUR 10,00 2
10 booking 5 EUR 5,00 2
11 booking 6 EUR 2,00 3
12 booking 7 EUR 1,00 1

#10

Hi :slight_smile:
Arrrgh! Did you have a copy of the data or manage to do undo to get the
data back?

If we are in data-recovery then;
1. did you email the file to anyone (even yourself)?
2. any chance it's on a Cd or Dvd laying around somewhere?
3. any chance it's on a usb-stick somewhere?
4. on a network drive somewhere?
5. a Cloud such as google-drive?
6. some formal back-ups?

Regards from
Tom :slight_smile: