Changeset 25547 in project

11/21/11 21:48:09 (9 years ago)
Alaric Snell-Pym

gazette: Split the omelette (insert pun about breaking eggs) between issues 20 and 21, at a suitable point near half-way, with a bit of link text.

2 edited


  • gazette/src/issues/

    r23724 r25547  
    313313   * bank.balance -230
    315 Note a few tricky things. Each transactions "splits", as the lines
     315Note a few tricky things. Each transaction's "splits", as the lines
    316316within them are known, have to sum to zero for everything to balance
    317317correctly, which tells us that nothing has gone missing. So when we
    394394third party.
    396 Now, to make things easy, I parse the ledger by just defining a heap
    397 of procedures and macros, and then diving into the ledger with
    398 eval. It's just Scheme code that, as it is executed, builds up the
    399 data structures. We'll need some helpers to set up third parties
    400 properly:
    402 <enscript>
    403 (define (define-third-party name address group)
    404         (let* ((balance-account
    405                 (make-account
    406                  (string-append name ".balance")
    407                  'balance
    408                  group
    409                  #f))
    410                (expenses-account
    411                 (make-account
    412                  (string-append name ".expenses")
    413                  'delta
    414                  'expenses-reclaimed
    415                  #f))
    416                (third-party
    417                 (make-third-party
    418                  name address balance-account expenses-account)))
    419           (account-third-party-set! balance-account third-party)
    420           (account-third-party-set! expenses-account third-party)
    421           (register-account! balance-account)
    422           (register-account! expenses-account)
    423           (set! (hash-table-ref *third-parties* name) third-party)))
    424 </enscript>
    426 Now we can work on a way of representing bills and invoices. A nice
    427 input syntax would be:
    429 <enscript>
    430 (define-third-party "clients.widgetcorp" "123 Any Street" 'clients)
    431 (register-account! (make-account "" 'delta 'income #f))
    432 (register-account! (make-account "" 'delta 'travel #f))
    433 (register-account! (make-account "stock.balance" 'balance 'stock #f))
    434 (register-account! (make-account "cash" 'balance 'cash #f))
    435 (register-account! (make-account "taxes.vat" 'balance 'vat #f))
    437 (invoice "INV005" "clients.widgetcorp" (ymd 2011 04 25)
    438          (service "" 800 (vat20) "Router setup and installation")
    439          (sale "stock.balance" 350 (vat20) "1 of LX300 router, serial number 0343248")
    440          (expense (ymd 2011 03 02) "" "cash" 35 () "Travel to site"))
    441 </enscript>
    443 The idea is that the sales taxes incurred on a line are specified as a
    444 list after the amount. If there's no taxes due, then we use an empty
    445 list. Otherwise we have a list of taxes, which are either plain tax
    446 names (to have the system compute the tax due itself) or two-element
    447 lists joining a tax name to a precomputed amount (often, when we pass
    448 on an expense, we know the tax we paid as it's on the receipt, so we
    449 should use that (even if they made a mistake working it out) rather
    450 than calculating our own).
    452 A nice way to implement that might be to make "invoice" a macro that
    453 absorbs its first three arguments as an invoice code, the name of the
    454 third party to invoice, and the date; then treats the rest as a body
    455 to be wrapped in a dynamic environment in which a parameter allows
    456 {{sale}}, {{expense}}, and {{service}} to add lines to the
    457 invoice. This is easily arranged:
    459 <enscript>
    460 (define-record date year month day)
    462 (define-record invoice
    463   name date third-party lines)
    465 (define (register-line! invoice line)
    466   (invoice-lines-set! invoice
    467                       (cons line (invoice-lines invoice))))
    469 ;; Compute sales taxes
    470 (define (compute-tax tax amount)
    471   (case tax
    472     ((vat20) (* 0.20 amount)) ;; Current UK rate
    473     ((vat15) (* 0.15 amount)) ;; Previous UK rate
    474     ((vat175) (* 0.175 amount)))) ;; Previous UK rate
    476 ;; Expand a list of taxes, some of which might be bare symbols
    477 ;; naming taxes to work out, or (<tax> <amount>) lists for
    478 ;; ready-computed taxes, into an alist of tax names to tax amounts
    479 (define (resolve-taxes amount taxes)
    480   (map (lambda (tax-desc)
    481          (if (list? tax-desc)
    482              (cons (car tax-desc) (cadr tax-desc))
    483              (cons tax-desc (compute-tax tax-desc amount))))
    484        taxes))
    486 (define-record invoice-service-line
    487   income-account amount taxes description)
    489 (define-syntax service
    490   (syntax-rules ()
    491     ((service income-account amount taxes description)
    492      (register-service! (*current-invoice*) income-account amount 'taxes description))))
    494 (define (register-service! invoice income-account amount taxes description)
    495   (let ((service
    496          (make-invoice-service-line
    497           (find-account income-account)
    498           amount
    499           (resolve-taxes amount taxes)
    500           description)))
    501     (register-line! invoice service)))
    503 (define-record invoice-sale-line
    504   stock-account amount taxes description)
    506 (define (register-sale! invoice stock-account amount taxes description)
    507   (let ((sale
    508          (make-invoice-sale-line
    509           (find-account stock-account)
    510           amount
    511           (resolve-taxes amount taxes)
    512           description)))
    513     (register-line! invoice sale)))
    515 (define-syntax sale
    516   (syntax-rules ()
    517     ((sale stock-account amount taxes description)
    518      (register-sale! (*current-invoice*) stock-account amount 'taxes description))))
    520 (define-record invoice-expense-line
    521   expense-account payment-account amount taxes description)
    523 (define (register-expense! invoice date expense-account payment-account amount taxes description)
    524   (let ((expense
    525          (make-invoice-expense-line
    526           (find-account expense-account)
    527           (find-account payment-account)
    528           amount
    529           (resolve-taxes amount taxes)
    530           description)))
    531     (register-line! invoice expense)))
    533 (define-syntax expense
    534   (syntax-rules ()
    535     ((expense (ymd year month day) expense-account payment-account amount taxes description)
    536      (register-expense!
    537       (*current-invoice*)
    538       (make-date year month day)
    539       expense-account
    540       payment-account
    541       amount
    542       'taxes
    543       description))))
    545 (define *current-invoice* (make-parameter #f))
    546 (define *invoices* (make-hash-table))
    548 (define-syntax invoice
    549   (syntax-rules (service sale expense)
    550     ((invoice name third-party (ymd year month day) body ...)
    551      (let ((inv
    552             (make-invoice name
    553                           (make-date year month day)
    554                           (hash-table-ref *third-parties* third-party)
    555                           '())))
    556        (parameterize
    557         ((*current-invoice* inv))
    558         (begin body ...))
    559        (set! (hash-table-ref *invoices* name) inv)
    560        (generate-invoice-transactions! inv)))))
    561 </enscript>
    563 We end the expansion of the {{invoice}} macro with a call to
    564 {{generate-invoice-transactions!}}, which will do the task of creating
    565 the double-entry transactions for the invoice. Other types of summary
    566 structure can be added by calling additional generator procedures at
    567 this point. This is largely a matter of going through the invoice
    568 lines, handling them on a case-by-case basis to generate lists of
    569 transaction splits that we can append together to generate the invoice
    570 transaction. The case of expense lines is interesting, in that an
    571 extra transaction has to be generated for each expense, to record its
    572 initial spending, as well as a split to record the expense being
    573 claimed in the invoice.
    575 For now, let's just handle one case:
    577 <enscript>
    578 (define (generate-invoice-transactions! inv)
    579   (register-txn! (make-txn
    580     (invoice-date inv)
    581     (string-append "Invoice " (invoice-name inv) " for "
    582                    (third-party-full-name (invoice-third-party inv)))
    583     (let ((txn-balance-account
    584            (third-party-balance-account
    585             (invoice-third-party inv))))
    586       (flatten
    587        (map
    588         (lambda (line)
    589           (cond
    590            ((invoice-expense-line? line)
    591             (list)) ;; FIXME: Not implemented
    592            ((invoice-sale-line? line)
    593             (list)) ;; FIXME: Not implemented
    594            ((invoice-service-line? line)
    595             (list
    596              (make-txn-split
    597               (invoice-service-line-income-account line)
    598               (- (invoice-service-line-amount line))
    599               (invoice-service-line-description line))
    600              (make-tax-splits
    601               (invoice-service-line-taxes line)
    602               txn-balance-account)
    603              (make-txn-split
    604               txn-balance-account
    605               (invoice-service-line-amount line)
    606               #f)))))
    607         (invoice-lines inv)))))))
    609 (define (make-tax-splits taxes txn-balance-account)
    610   (map (lambda (tax)
    611          (let ((tax-type (car tax))
    612                (tax-amount (cdr tax)))
    613            (case tax-type
    614              ((vat20 vat15 vat175)
    615               (list
    616                (make-txn-split
    617                 (find-account "taxes.vat")
    618                 (- tax-amount)
    619                 #f)
    620                (make-txn-split
    621                 txn-balance-account
    622                 tax-amount
    623                 #f))))))
    624        taxes))
    625 </enscript>
    627 Feeding in the above example invoice, then checking out the resulting
    628 double-entry transaction list, shows that it worked:
    630 <enscript>
    631 (hash-table-for-each *txns*
    632                      (lambda (date txns)
    633                        (for-each
    634                         (lambda (txn)
    635                           (printf "Date: ~A Desc: ~A\n"
    636                                   (txn-date txn)
    637                                   (txn-description txn))
    638                           (for-each (lambda (split)
    639                                       (printf "Acct: ~A Delta: ~A Notes: ~A\n"
    640                                               (account-name (txn-split-account split))
    641                                               (txn-split-amount split)
    642                                               (txn-split-notes split))) (txn-splits txn)))
    643                         txns)))
    644 </enscript>
    646    date: #<date> Desc: Invoice INV005 for Widget Corp
    647    Acct: Delta: -800 Notes: Router setup and installation
    648    Acct: taxes.vat Delta: -160.0 Notes: #f
    649    Acct: clients.widgetcorp.balance Delta: 160.0 Notes: #f
    650    Acct: clients.widgetcorp.balance Delta: 800 Notes: #f
    652 We've ended up with multiple splits for the same account, as we record
    653 that both VAT and the money due for the service are to come from the
    654 client's balance account - and other splits will add plenty more. To
    655 fix this, we need to write a procedure that canonicalises a list of
    656 splits, and call that on the splits before calling
    657 {{make-txn}}. Canonicalisation consists of finding all the splits that
    658 refer to the same account and have the same notes (be it {{#f}} or a
    659 string) and merging them into one with the total of the amounts. But
    660 I'll leave that (along with implementing bills, payments, and some
    661 actual reports) as an exercise to the reader... It's easy to imagine
    662 how to generate a VAT report from the list of transactions, by
    663 filtering them for membership of the required date range and looking
    664 for splits involving "taxes.vat", or to generate a nicely formatted
    665 invoice by extracting a single invoice record, or to work out the
    666 balance of an account at any point in time by adding up all the
    667 transaction splits that involve it up to that point in time. Also, the
    668 core engine needs to be wrapped up in a module that only exposes the
    669 required bindings, and hides internals.
    671 Having automated one's book-keeping and financial reporting, many
    672 operations (such as the VAT returns) can be done without involving an
    673 accountant; in my case, the accountant is only needed to help with the
    674 annual corporation tax computation and filing of official accounts,
    675 which requires deep understanding of the UK tax system to do
    676 everything properly. Having said that, if I studied the system
    677 properly (and tracked the changes each year), I'm sure I could
    678 automate that, too...
     396Accounts are the organisational structure that turns a sea of financial
     397events into something we can start to make sense of. In the next thrilling
     398installment, we will look at actually entering transactions, and turning them into
     399a data structure from which useful reports can be produced...
    680401== 4. About the Chicken Gazette
  • gazette/src/issues/

    r25546 r25547  
    334334"financial book-keeping in scheme" series. If you missed the first part,
    335335check it out [[|here]].
    337 The dream of many Schemers is to write Scheme for a living. Due to the
    338 regrettable tendency of employers to frown upon good ideas, the best
    339 way to achieve this is to start your own company, so you can be your
    340 own boss, and frown upon the good ideas of others instead.
    342 However, running your own company comes at a price; and a big part of
    343 that price is the horror of "book keeping"; the requirement to track
    344 all flows of money and other, more abstract, forms of value in and out
    345 of your company.
    347 Some people will tell you book-keeping is simple. "Just keep all your
    348 receipts and bank statements and bills and invoices", they say. "Then
    349 send them to your accountant at the end of the year."
    351 "But what about value-added tax?", you ask (or "sales tax" in some
    352 countries). "And what about income tax paid at source for my employees
    353 (including myself)?". "And why do I need to pay an accountant so much
    354 to do a job that a computer can easily do in milliseconds?" And then
    355 the smug smile slowly drips from the face of the "Oh it's easy" crowd.
    357 Clearly, book-keeping is complicated. And yet also simple, in that it
    358 is determined by sets of rules.
    360 We know what to do, don't we? Let's get coding!
    362 The problem is that book-keeping involves several different kinds of
    363 inputs - bills (that people send you), invoices (that you send
    364 people), transfers of money (bills and invoices being paid), loans (to
    365 and from the company), employees being paid, interest payments from
    366 the bank, dividend payments, and so on; while it also involves several
    367 different outputs - tax reports for the various taxes involved (in the
    368 UK, I had to deal with VAT every three months, income tax and national
    369 insurance when paying myself and my wife as employees every month, an
    370 annual filing fee, and annual corporation tax and dividend payments),
    371 statutory filing of certain financial summaries (generally annually),
    372 and internal reporting: How much was I spending? How much did each
    373 client owe? How much should be in the bank by when? Plus, it's nice to
    374 be able to generate nice invoices to send to folks who owe you
    375 money. That's a form of specialised report, too, just reporting on a
    376 single invoice.
    378 Each of the output reports depend in complex ways on different
    379 information from the inputs. The VAT reports mainly have to add up how
    380 much VAT I've paid when being billed by others, and how much VAT I've
    381 charged when invoicing - meaning that VAT needs to be tracked on all
    382 bills and invoices so it can be extracted. They also want to know
    383 totals of actual money in and out of the company in the period (even
    384 stuff where VAT isn't an issue), presumably to check up on
    385 me. Meanwhile, end of year reports tend to need to know how much I've
    386 invoiced for various different kinds of work, and what I've spent on
    387 what kinds of things: buying equipment that will last me for several
    388 years is handled differently to expenses like travel, or buying stuff
    389 that I eventually resell to clients (so in our invoices, we need to
    390 keep track of money charged for services separately to money charged
    391 for things).
    393 Some reports care about virtual money moving hands. As soon as I
    394 invoice somebody, then the company now has a virtual asset - some
    395 money owed to it. That's worth as much as cash in the bank from some
    396 perspectives (generally, I have to pay tax on it as soon as it's
    397 invoiced, even if I've not been paid). And yet some care only about
    398 actual cash changing hands (working out my bank balance, for instance).
    400 Sometimes our clients invite us to incur expenses in doing work for
    401 them (such as extra travel) and then invoice them on for those
    402 expenses, so they pay us back - in which case, expenses need to be
    403 able to be tied to invoices, as well. Sometimes we decide to cancel an
    404 invoice, which can't be done by just pretending it never existed, for
    405 audit-trail reasons; we need to issue a "negative" invoice called a
    406 credit note.
    408 Just to complicate matters more, the actual movement of money isn't
    409 atomic. If I invoice somebody on date A, they might post me a cheque
    410 which arrives on date B, which I pay into the bank on date C, which
    411 actually clears into the account (and thereby appears on my bank
    412 statement, when I get it) on date D. So at date A the company now has
    413 a "we are owed" pretend-money asset, which goes through various stages
    414 until it finally turns into money in the bank on date D.
    416 I handled my book-keeping with some hacky scripts written in Chicken
    417 Scheme. What I'm going to document here is partly what I've done, and
    418 partly what I should have done - it was a very iterative process,
    419 refining the best way to handle stuff, and there's lots of
    420 improvements I've wanted to make but not had time to. So I'm going to
    421 describe the ideal case, not the hacky half-way house I actually have
    422 right now!
    424 The approach I took was to have a file called a "ledger" that I enter
    425 all my invoices and so on into. This is parsed to build up a bunch of
    426 data structures in memory, from which the various reports can easily
    427 be obtained. Firstly, for each kind of input object (invoices, bills,
    428 etc) there's a list of them, or more often a hashmap to make them easy
    429 to find by some identifier (I can give my invoices unique symbolic
    430 names, for instance). That contains the raw data as parsed from the
    431 ledger file. But then we also create summary structures, which are
    432 used by the more general reports to generate their output without
    433 having to special-case each and every different input object type, and
    434 to enable sharing of common functionality between reports.
    436 The main summary structure is the double-entry transaction list, which
    437 models the entire financial activity of the company as transfers
    438 between accounts.
    440 Imagine I invoice Widget Corp for setting up and installing a router:
    442   INVOICE INV005: Issued 2011-04-25
    443   Router setup and installation: GBP 800
    444   1 router from my stock: GBP 350
    445   1 train ticket for me to go to their site: GBP 35 (no VAT due)
    446   Subtotal: GBP 1,185
    447   VAT on the above: GBP 230
    448   Total due: GBP 1,415
    450 As part of the work, I lose a router (worth GBP 350) from my stock,
    451 and have to spend GBP 35 on a train fare.
    453 This might expand into the following transactions:
    455  * 2011-03-02: "Expense for Widget Corp (INV005)"
    456    * +35 "Travel to site"
    457    * cash -35
    459  * 2011-04-25 "Invoice Widget Corp (INV005)"
    460    * -800 (Router set up and installation)
    461    * stock.balances -350 (1 of LX300 router, serial number 0343248)
    462    * clients.widgetcorp.expenses -35 (Travel 2011-03-02)
    463    * taxes.vat -230
    464    * clients.widgetcorp.balance +1415
    466 And, eventually, they might pay me, which hits my bank account some
    467 time later:
    469  * 2011-05-07 "Payment from Widget Corp (INV005)"
    470    * clients.widgetcorp.balance -1415
    471    * bank.balance +1415
    473 And then one day I'll pay my VAT bill, which will look something like:
    475  * 2011-06-01 "VAT payment for period from 2011-03-01 to 2011-06-01"
    476    * taxes.vat 230
    477    * bank.balance -230
    479 Note a few tricky things. Each transactions "splits", as the lines
    480 within them are known, have to sum to zero for everything to balance
    481 correctly, which tells us that nothing has gone missing. So when we
    482 start being owed GBP 1,415 by Widget Corp, we need to account for
    483 where that asset has come from. Special accounts with names such as
    484 "" (for value generated by me working) and
    485 "clients.widgetcorp.expenses" (for previously-paid expenses that, as
    486 of this invoice, I can charge the client for) pop into
    487 existence. "taxes.vat" looks as if VAT is a form of income for me, as
    488 money comes "from" it in the transaction - which is sort of true; I'm
    489 charging Widget Corp for some VAT alongside for the actual work
    490 done. Figuring out what signs to put on all the items in the invoice
    491 is mind-bending and painful, but if you just concentrate on making it
    492 all add up to zero in the end and starting from things that are
    493 obvious (is money going into or out of the bank account, or the "owed
    494 to me by this customer" account?), you can figure it out.
    496 From the above, we can start to flesh out some data structures:
    498 <enscript>
    499 (define-record txn
    500                date customer code description splits)
    502 (define-record txn-split
    503                account amount notes)
    506 (define *txns* (make-hash-table))
    507 (define (register-txn! txn)
    508   (if (hash-table-exists? *txns* (txn-date txn))
    509       (begin
    510         (set! (hash-table-ref *txns* (txn-date txn))
    511               (cons txn
    512                     (hash-table-ref *txns* (txn-date txn)))))
    513       (begin
    514         (set! (hash-table-ref *txns* (txn-date txn))
    515               (list txn)))))
    516 </enscript>
    518 What is an "account"? There's a few kinds, and what kind of account it
    519 is matters in reporting. Accounts might be assets within the company -
    520 such as "clients.widgetcorp.balance" or "bank.balance" or
    521 "stock.balance". Or they may be places where money (be it real or
    522 virtual) is created from or destroyed by (from the perspective of the
    523 company), such as "" and "". The important
    524 difference is that balance-type accounts have a balance that is
    525 increased when money is sent to them and decreased when it's taken
    526 out, and that balance is part of the value of the company, while the
    527 income/expense type accounts don't. In my terminology, these are
    528 "balance" accounts and "delta" accounts. Each account also begins to a
    529 group, used to aggregate them in reports: there's income accounts,
    530 bank accounts, client accounts, and so on. And accounts may be tied to
    531 a third party - I've given an example of a client above, but also, the
    532 organisations that send me bills have balances (the money I owe
    533 them). In general, every third party (be they ones that bill me, or
    534 ones that I invoice, or both - I've interacted with other freelancers,
    535 sometimes working for them, sometimes vice versa) has a set of
    536 accounts attached to them for their balance, expenses I can claim from
    537 them, and so on. That implies another set of record types:
    539 <enscript>
    540 (define-record third-party
    541                name address balance-account expenses-account)
    543 (define-record account
    544                name type group third-party)
    547 (define *third-parties* (make-hash-table))
    548 (define *accounts* (make-hash-table))
    550 (define (find-account acct-name)
    551   (hash-table-ref *accounts* acct-name))
    553 (define (register-account! acct)
    554   (set! (hash-table-ref *accounts* (account-name acct)) acct))
    555 </enscript>
    557 An account's {{third-party}} slot may be {{#f}} if it's not part of a
    558 third party.
    560337Now, to make things easy, I parse the ledger by just defining a heap
Note: See TracChangeset for help on using the changeset viewer.