Tracking Finances in Spreadsheet

[blackpatch_]

Hello i'm new to open office and tables/spreadsheets in particular, so please be kind.

I've managed to set up a table of 5 columns with unlimited rows. The purpose to the table is to keep track of my finances over time with a Runing Balance.

So far i've filled in the columns with my financial data for the next 6 weeks ahead and thats working fine, as it shows a cash projections for the 6 weeks ahead and a day by day running balance. The 5 columns I am using are Item, Date, Cash In, Cash Out and Balance. Problem is I am running into difficulties if an unforseen expense occurs - so for the moment I am having to manually adjust the balance figures for the 6 weeks ahead of the unforseen expense.

I think I need to apply rules or formulas to the 3 columns in question namely: Cash in, Cash Out and Balance, but don't know how to do that. I'm ill at the moment so there is a limit to what i can do to resolve this myself.

Hoping that somene can spoonfeed me how to do this.

Thanks in Advance. Ian.

Title Edited. A descriptive title for posts helps others who are searching for solutions and increases your chances of a reply. (TheGurkha, Moderator)

[vasa1]

For
Col 1 = item >>> A1
Col 2 = Date >>> B1
Col 3 = Cash in >>> C1
Col 4 = Cash out >>> D1
Col 5 = Balance >>> E1

Let's say your opening balance is $100.00

Type 100.00 into E2 (without any curency signs)
In E3 type =E2+C3-D4 and hit enter
Copy the contents of E3 and fill down as much as you need.

So you need just one formula in Cell E3.

As you fill in values in Cols 3 or 4 subsequently (without typing any currency signs), your running balance will reflect the updates.

[keme]

Accounting in a spreadsheet easily becomes an unmanageable mess. (As soon as you move, insert, or sort rows, you risk breaking formulas with references acros rows.) Work in a database, and preferably use a purpose built tool.
If you must use a spreadsheet, use the OFFSET() function instead of direct references to fetch values from previous/next row.

Three options that I looked at: GnuCash, Buddi, and MoneyManager ex. On first impression, they all look good, and they all have installers for MS Windows, Apple OS-X and Linux, (and offer source code, which should make it available on any conceivable platform), so you're not lost if you switch to a different system.

There are many other titles out there. I am just now starting to evaluate the three I mentioned, and my impressions are:

Buddi seems to be the easiest to get started with. It offers basic functionality, but as far as I can see it doesn't provide for much customisation. There's no transaction splitting (each transaction will have one credit and one debit entry)

GnuCash is a bit more advanced, provides flexible double entry balancing (spliting to multiple entries per transaction) and allows for creating custom reports. It also has integrated support for online banking.

MoneyManager EX looks like it offers about the same flexibility and options as GnuCash. Integration of online bank transactions seems to be missing, but it has a menu option to run SQL commands (which, among other things, should allow creation of reports of any complexity). I haven't tried it out, but on first look it seems to cover most needs, and the user interface seems to be the most intuitive of the three (but of course that's largely a matter of taste).

So if you just need to keep track of the cash flow, Buddi may be the optimal choice. If you want tools for budgeting and future planning, and more detailed insight to what you spend money on, the other titles may be what you need.

[vasa1]

Hi Keme,

Thanks for your comments. Will learn as much as I can about OFFSET() since I do, sometimes, have the exact problems you mention.

(As for databases, I seem to have a mental block and could never get the hang of them .)

I will also look at the other software suggested.