--- title: A Robust Reporting System for Ledger date: '2012-01-01 10:01:14' tags: Personal Finance, Ledger, Projects, Ruby id: 3a32a topic: Finance description: The reporting system I use to manage my financial life. Reports are just HTML and ERB with a smattering of SQL. --- *Note: you can find much more information about ledger on [ledger-cli.org](http://ledger-cli.org), including links to official documentation and other implementations* For the last five years I've kept my personal finances in order using the ledger system, a sophisticated command line program that consumes a lightly formatted text file. It's helped me repay debts and get everything in order, helping me financially absorb an injury last month that would have been extremely detrimental just a few years prior. The stock ledger program is exclusively command-line oriented. For quick checks and `grep`ing over output, this is fine. For some time, though, I've wanted a more graphical, more robust way of looking at my finances. I've also wanted a more familiar query language, since version 2.0's queries were someone limited and version 3.0's query syntax is not very well documented yet. Last year I wrote a [simple system](/program-your-finances-reporting-for-fun-and-profit) that pushed monthly reports out to static HTML files, which got me part of the way there but I really wanted something more flexible. Something where I can just write an arbitrary query and have the output dumped to HTML. Thus, I present [Ledger Web](https://github.com/peterkeen/ledger-web). In Ledger Web, your ledger is kept in a text file, just the same as always, reports are ERB files, and queries are SQL. Ledger Web watches your ledger file and whenever it changes dumps it into a PostgreSQL database table. It's also quite customizable, letting you set hooks both before and after row insertion and before and after ledger file load. ### Installation Ledger Web installation is pretty simple. First make sure you have PostgreSQL version 9.0 or greater installed on your machine. Then, run these commands: ```bash $ gem install ledger_web $ createdb ledger $ ledger_web ``` Then, open your web browser to [http://localhost:9090/](http://localhost:9090/) where you'll see some simple example reports. ### Example Report Let's walk through a simple pair of reports that shows off most of Ledger Web's features. Yesterday I ran across this [blog post](http://earlyretirementextreme.com/your-budget-is-like-sinking-ship.html) which draws a comparison between a typical person's budget and a wooden ship, always springing leaks and at risk of sinking to the bottom. I decided to write a report that shows my expenses both summed by year and broken out into individual lines. First, the Leaky Ship report itself: ```erb <% @query = query({:pivot => "Year"}) do %> select account as "Account", xtn_year as "Year", coalesce(sum(amount), 0) as "Amount" from accounts_years left outer join ( select xtn_year, account, amount from ledger ) x using (account, xtn_year) where account ~ '(Income|Expenses)' and xtn_year <= date_trunc('year', cast(:to as date)) group by account, xtn_year order by account, xtn_year <% end %>