Build Your Own Simple Linux Banking Tool

In general, the numerous spreadsheet templates that are Microsoft Excel clones and load in OpenOffice and LibreOffice are digital versions of a paper-and-pen checkbook register. If you want a simple spreadsheet to enter deposit and withdrawal details to get a running account balance, these basic spreadsheet solutions are not going to do the trick.

The Linux OS lacks an effective yet simple checking and banking tool.

There are money-management tools for Linux users, of course, but most full-fledged offerings are overkill for monitoring spending patterns and balancing financial accounts. In general, Linux financial applications are either too complex to use without an accounting degree or simple, spreadsheet-style apps that do little more than help you keep an electronic copy of your deposits and withdrawals.

I avoided for years confronting this affront. I tolerated a commercial Windows program -- AceMoney by MechCAD Software -- running in Wine to fill the void. I brought it with me as must-have software years ago when I made the leap from messing around with the Linux OS to adopting it full-time on all of my computers.

Wine evolved, however, and my various Linux distros progressed. The results produced a series of performance failures with the aging Windows program version I imported all those years ago. Eventually, it was time to buy a current version of the financial tracking software or replace it with a Wine-less open source money tracker application.

Linux Rocks Most Times

Linux has not disappointed me in fulfilling most of my software needs over the years. I have found really great Linux applications to serve 99 percent of my personal and professional computing needs.

Besides falling short with simple checking and investment tracking tools, the main other disappointment for me has been not having a Linux version of any major tax preparation software. In addition, I am still using a Vista-only Windows phone recorder software-and-hardware bundle to record interviews. The manufacturer abandoned driver support after that OS fiasco.

Personal Checkbook Register 2.0
Personal Checkbook Register 2.0 is a simple spreadsheet template with formulas in cells to total banking entries. (click to enlarge)
Finding a suitable Linux app for my personal and light business banking needs, however, was more difficult than you might think. I have reviewed and actually used over the years various personal financial manager Linux apps.

I even sought workable alternatives to full-fledged Linux business applications by using third-party spreadsheet templates. I played around with creating my own spreadsheets with formulas in various cells to duplicate what my erstwhile Windows money software did.



A Solution in the Making

Typically, the numerous spreadsheet templates that are Microsoft Excel clones and load in OpenOffice and LibreOffice are digital versions of a paper-and-pen checkbook register. If all you want is a simple spreadsheet to enter deposit and withdrawal details to get a running account balance, these basic spreadsheet solutions are ineffective.

Instead, the solution requires a spreadsheet template that includes more data. For example, the template needs row and columns to enter date, check number, purpose and amount. With this additional information, you can use the various filter and sort functions of the spreadsheet program to extract your own customized tracking and spending reports.

A workable solution also requires more spreadsheet design finesse than simple templates with =SUM cell formulas to total individual checkbook, credit card, investment and savings account entries.

The Spreadsheet Connection

I finally discovered a very workable solution for tracking my checkbook spending along with personal and business expenses and basic investment monitoring. The solution is a cross-platform remedy that works with open source spreadsheet office applications such as LibreOffice and OpenOffice.

This Linux solution works with any financial program in Windows that can read an Excel spreadsheet. When I have to take my financial records on the road and view them in Windowsland, the same spreadsheet files I use in Linux are accessible there also.

Specifically, the solution involves using a freeware program that has a purchase option for a few additional but very automated functions through macros. The spreadsheet runs in Excel and OpenOffice/LibreOffice on Windows and Linux platforms.

A Distro-Agnostic Solution

Keep in mind that this approach to tracking your finances involves using spreadsheet templates to get started. These downloaded files are not standalone software Linux packages. Even if they appear to open and run as standalone worksheets in their own application windows, they are nothing more than templates opened by an OpenOffice or LibreOffice Calc application. That makes this solution distro- and package-agnostic, with nothing to install.

After several months of being free of my holdover AceMoney program, I have kept my finance tracking up to date with no more effort than when I used the dedicated checkbook program. I expanded the functionality by combining a series of separate spreadsheets adapted from the downloaded template into one big spreadsheet file.

I had created several separate spreadsheet templates from the downloaded template file. At first I set up a separate template sheet for each of my banking and financial tracking needs. Then I discovered that I could use the same copy and paste operations to combine all of my account tracking registers into their own tabs in one massive spreadsheet file.

The Details

Through trial and error, I narrowed down all of the available spreadsheet template and checkbook register sheets I found to two. I found Personal Checkbook Register 2.0, released early this year, to be a suitable performer -- if I only wanted to maintain a checkbook register.

Personal Checkbook Register is a glorified simple spreadsheet with formulas already in the totals cells to show a running checkbook balance. It is nothing more than an electronic version of what you do manually in the back of your checkbook. The only automated feature is the balance calculation. You still have to do checkbook reconciliation with the directions on the back of your bank statement.

Check Register Spreadsheet template
A worksheet built into the Check Register Spreadsheet template makes it easy to reconcile monthly statements. (click to enlarge)
What became a much better solution is the free version of Check Register Spreadsheet version 4.5. This is a much handier spreadsheet template with a few easy-to-use features beyond the sort and filter options available in any spreadsheet. For a nominal fee, you can get the paid template that has a few nifty macros included.

The author, who identifies himself only as Michael, is a financial blogger on several websites. His background in practical approaches to tracking your money is evident in how he designed his checkbook register template.

Using It

Check Register Spreadsheet has two features that are missing from Personal Checkbook Register and other spreadsheet files like it. One is its ability to automate the process of reconciling your monthly banking statements; the other is Check Register Spreadsheet's additional linked sheets that let you create customized lists of transaction types and categories. You set up each function in its own sheet in the spreadsheet file, then you click the drop-down arrow that appears when you click in the cell while making a new entry.

The top of the spreadsheet has special cells where you enter the balance from the current banking statement. You then enter a "c" (for canceled) in the column to the immediate left of the balance column for each entry that is listed in your monthly statement. In the cell directly under your statement balance you will see a running total of the amount of money in your account left to reconcile. When all of the checkbook entries equal the number of canceled debits and credits on your monthly statement, the amount left to reconcile shows as $0.

Have it Your Way

I started using Check Register Spreadsheet as a replacement for the checkbook program I retired. I modified the categories transaction types to create separate spreadsheet files for several other accounts for both my writing income, personal expenses, business expenses and investment accounts.

Then I realized I could consolidate these separate files into one larger spreadsheet file that contained all of my accounts. It was as simple as creating a new sheet tab and pasting the contents of a separate spreadsheet file into an existing one.

One of the original sheet tabs in Check Register Spreadsheet was labeled "Recurring Transactions." I modified that idea for the other account tabs to provide notes and information. In that tab you can enter up to 50 transactions that regularly occur. The money amounts entered in this tab sheet do not affect the actual account balances. This is merely a simple way to conveniently maintain a log of ongoing money amounts.

Customize Your Tracking

The free -- think open source equivalent here to keep it simple -- version of Check Register Spreadsheet has several other features that make using this template more effective than other template files you might find. For instance, it allows you to set up and monitor your spending with up to 200 categories.

You can also create up to 15 transaction types such as Check, ATM, ETF, POS, Withdrawal, Deposit and Transfer. Remember, this is a spreadsheet template. So, the more precise you are with listing each type of financial transaction and spending category you handle, the more flexibly you can monitor your spending.

For example, it takes very little effort to use the spreadsheet menu functions in OpenOffice Calc and LibreOffice Calc to filter and sort and generate a report or chart display of your money usage. Use this same approach to adapt this template to track your credit card accounts and your various loans, savings and business accounts.