Message Boards Message Boards

[Project] Double entry accounting and cash flow forecasting

UPDATE 2: I would love to know if you download anything from the repository. Even if you don't use git, you can download everything in a zip file directly.

UPDATE 1: I've now put some preliminary sketches up in the Double folder on my github repository. Comments on design issues welcome, and if anything is unclear please let me know.


Is anyone using Mathematica for basic accounting purposes? I am responsible for book-keeping and preparation of final accounts for 2 businesses. I've used Excel in the past in other business contexts but found it too unreliable, and it doesn't have the checks and balances that a proper accounting package has. But I found commercial accounting packages inflexible and awkward for small business use, as well as being too expensive. I've used Mathematica for my own accounts as well as for some simple business analysis and cash flow forecasting. I find myself wanting a double-entry system with decent cashflow forecasting and nested accounts (for instance, I'd like to generate a report that aggregates all food sales, whereas I've separate accounts for different food items); the closest thing that's free/accessible for me is gnucash but it doesn't do cashflow and sales tax/VAT and invoicing is a bit of a pain. I've already built an import from html (from my banking website), an export to .qif (for gnucash &c), a system for defining a tree of accounts and associating string pattern matches to individual accounts in the tree and a sketch of a simple design of a transaction database and user interface using associations/Query. My initial idea is to start an open software development on github; I don't have the time to build everything I'd like myself.

An additional aim is to understand accounting better and I'd welcome input on issues of system correctness, design and the potential interest such a system might generate. As I work as a maths teacher in Further Education, I also wonder about the potential to use Mathematica to teach accounting, theory & practice.

If there is any interest here I will find some time to anonymise my data and post a notebook with design notes and code samples. If there is serious interest or if anyone is interested in a commercial partnership I might rethink my plan to publish it all, but somehow I doubt this.

15 Replies

Hi Matthew

I have only just come across this post.

I completed an accounting project earlier this year. Features included:

Hierarchical classification of line items

Dynamic display of financials

Full support for journalling and trial balance

Full auditing trail of ledger entries

Automatic report generation including automatic creation of notes to the accounts

Export to PDF, CDF, XML, Excel

Data storage in MongoDB

You can see video of a very early version of the application here:

http://ibnhconsulting.blogspot.com.au/2014/10/a-dynamic-accounting-application.html

cheers

Mike

POSTED BY: Mike Honeychurch
Posted 9 years ago

Seems a lot of work for a very simple problem...double entry accounting. However open source makes it do-able.

POSTED BY: Raj Shah

I'm not quite sure what you mean Raj. The basic problem is perhaps simple, though if, as I do, you wish to include cash flow forecasting that can be as complex as you like depending on which financial instruments you wish to include. In its most general form, cash flow forecasting is very likely undecidable! Actually I would be interested to see a simple mathematical model that captures the essential matters such as hierarchical accounts, taxation, profit & loss and balance sheet creation and this might be straightforward to implement in a naive way in Mathematica. But creating an interface which makes entering and editing large amounts of data rapidly and effectively is not simple, by any means. There are some decent open-source solutions out there such as Gnucash (as well as some very popular, but in my limited experience frustrating commercial packages: the one I've used the most is Sage which seemed to me to be designed primarily to prevent fraud on the part of the bookkeeper which is not a concern of mine with this project). There are features I dislike and others missing in Gnucash (particularly nested accounts, imports from html, undo, VAT accounting and cash flow forecasting); and although I could contribute to the Gnucash project directly, I find the development cycle and its requirements to learn the details of the languages used and code design methods just too much of an investment. Hence this project.

Matthew, this seems like a great project - we have added it to our special collaboration board. I would be interested to see details. By the way in your profile you have a special field for your GitHub account in case you wish to link to your work there.

POSTED BY: Vitaliy Kaurov

I'm glad you like the idea Vitaly, I was a little bit surprised my suggestion didn't generate interest before but suddenly interest seems to have appeared. Thank you for adding it to the collaboration board. Perhaps we can use this thread to get an idea of which features folks are most interested in.

I've now put some preliminary sketches up in the Double folder on my github repository. Comments on design issues welcome, and if anything is unclear please let me know.

PS Vitaliy, thanks for the suggestion about adding a github page to my Profile, that's done now.

Thanks, @Matthew Fairtlough , great! I also updated your to post wit this info so people se the link right away. I think what would be useful if you show some use-cases on this thread, - the workflow with the tools you are developing - if possible.

POSTED BY: Vitaliy Kaurov

Thanks Vitaliy for the headline and I'll now learn how to use this feature for future updates.

Good suggestion about explaining the intended workflow.

Workflow

The scenario I envisage is a small business or sole trader already working with Mathematica and desiring to integrate their invoicing, accounting and financial planning requirements with financial or other data already processed with Mathematica. The eventual aim is to have a fully-fledged and reliable double-entry accounting system which produces taxation, balance sheet and profit and loss statements suitable for statutory reporting (e.g. annual accounts to Companies House in the UK, sales tax reports in the US (I guess you need these?) or quarterly VAT returns in the European Union). Another aim is to create a cash-flow forecasting mode which would allow financial plans to be kept integrated with the actual state of accounts as they evolve. It might at some stage be possible to produce a version that works inside CDF Player Pro which would create a cheaper alternative to the full Mathematica version for organisations who do not (yet) use Mathematica.

Please bear in mind that I am not an accountant and have had minimal formal accounting or bookkeeping training and I am very open to comments or corrections that would improve on these preliminary ideas.

Current workflow

I tend to use my bank statements as the starting point for preparing accounts so my current workflow is as follows:

  1. [Hand] Download .html from my banking website (smile: they do not provide accounts in standard accounting formats such as .qif)
  2. [Mma] Extract the transactions (the code evolves over time as the format of pages changes) as a table
  3. [Mma] Create and save any required changes to structure of accounts (renaming, creating or moving accounts)
  4. [Mma] Create any required mappings from the description field to specific accounts (this can save a lot of time in later steps; it is straightforward to use string patterns or regular expressions to define such mappings)
  5. [Mma] Export transactions into a .qif format file, assigning accounts using information provided in previous step
  6. [Hand] Import the generated file into an accounting package for the remaining bookkeeping and accounting work (Gnucash in my case)

The last 2 steps would be less important for me if I could use Mathematica all the way but the ability to export accounts into different formats is very useful.

Desired new workflow

Basically, replace Gnucash by Mathematica for the following tasks:

  1. Sales Invoice creation. This creates an entry in Accounts Receivable which will later (we hope!) be offset by income transaction(s).
  2. Purchase order creation. Dually, this creates an entry in Accounts Payable.
  3. Quarterly VAT report generation. Currently I get a report which needs further processing using a spreadsheet. This could be much better automated.
  4. Manual entry and editing of transactions, particularly for transaction splits (this is where, for example, a direct purchase from a bank account is split out into a VAT portion and a portion allocated to an expense account).
  5. Searching for transactions using date, description or value fields. Gnucash has quite limited search facilities and even when I have a suitable list of transactions I cannot perform any bulk actions such as deletion or moving. This is a desirable additional feature for this project.
  6. Trial balance, profit and loss and balance sheet preparation for specific accounting periods.
  7. Verifying transactions and checking them off at the end of an accounting period.
  8. Transferring balances at year end to clear accounts for the next accounting period. This provides a running account of the business finances that is very useful for checking against published accounts.
  9. Budgeting. I don't use this feature much as a proper cash-flow forecast is much more useful to me (Gnucash doesn't provide this).

Many of these steps imply other actions such as creating a new customer or supplier, creating a job number for invoicing, recording accounting dates and tax rates, posting an invoice to the accounts, setting up an initial chart of accounts, auto-completion in transaction fields, numbering transactions and so on. There is quite a lot involved in making the bookkeeping part of the job efficient, especially in creating and editing transaction splits. Gnucash is good here, but many action sequences could be automated.

Interesting ideas. Have you seen Automated Report Generation - this could automate some steps for you. See also Automated Reports.

Version 10 provides sophisticated tools for automated document creation. Create templates using the familiar notebook-editing process and apply them to any source of data, including computations, databases, web feeds, Wolfram|Alpha, and the Wolfram Language's integrated computable data sources. Easily customize the output and generate reports with interactive charts and dynamic computations that can be scheduled, shared, and annotated in the Wolfram Cloud.

POSTED BY: Marina Shchitova

Yes, thank you @Marina Shchitova I had seen this feature and played with it a little but hadn't thought of using it in this context. A very good idea and the great thing about it is that the templates can be very easily edited (unlike in some implementations such as Gnucash).

Hello Matthew interesting topic, soon I'll be sending notes to share my vision.

Yes please Juan!

Posted 9 years ago

Hi Matthew, I would be interested to see work along these lines. Do you have some work you could share as a starting point? Many thanks ahead of time.

POSTED BY: Reid Frasier

I do have some work I could share but it would take a while to clean it up; I can send a scruffy notebook with my initial ideas and coding if you like which might have some useful material in it. For a proper start though I think I should share something clear and simple so that the design can work well from the start and that will take a little time.

Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract