Some tax-time automation

I often struggle to find the right balance between automation and manual work. As it is tax time, and Chase bank only gives you 90 days of statements, I find myself every year going back through our statements to find any business expenses and do our overall financial review for the year. In the past I’ve played around with MS Money, Quicken, Mint and kept my own spreadsheets. Now, I just download the statements at the end of year and use acrobat to combine and ruby to massage the combined PDF into a spreadsheet.1

To do my analysis I need everything in a CSV format. After, getting one PDF, I end up looking at the structure of the document which looks like:

Earn points [truncated] and 1{aaa01f1184b23bc5204459599a780c2efd1a71f819cd2b338cab4b7a2f8e97d4} back per $1 spent on all other Visa Card purchases.

Date of Transaction Merchant Name or Transaction Description $ Amount
01/23 -865.63

12/29  NEW JERSEY E-ZPASS 888-288-6865 NJ  25.00

0000001 FIS33339 C 2 000 Y 9 26 15/01/26 Page 1 of 2

I realize that I want all lines that have a number like MM/DD followed by some spaces and a bunch of text, followed by a decimal number and some spaces. In regular expression syntax, that looks like:


which is literally just a way of describing to the computer where my data are.

Through using Ruby, I can easily get my expenses as CSV:

Boom. Hope this helps some of you who might otherwise be doing a lot of typing. Also, if you want to combine PDFs on the command line, you can use PDFtk thus:

pdftk file1.pdf file2.pdf cat output -

  1. The manual download takes about 10 minutes. When I get some time, I’m up for the challenge of automating this eventually with my own screen scraper and web automation using some awesome combination Ruby and Capybara. I also use PDFtk to combine PDF files. 

How much do IMA reservists make?

It is not easy to quickly decipher military pay tables for us IMAs. In order to do some recent financial planning, I had to calculate my pay. I’m an O-4 with between 14 and 16 years of service. Here is what I did.


You can find the official military pay tables from the DoD comptroller but I found to have the 2015 data that I couldn’t find on the official site.

Here, I saw that based on my years of service, my drill pay is \$962.83, which is 4 IDTs, or 16 hours, so I get paid \$60.17 an hour. For 48 IDT’s (Cat A), this means I get paid

$$ \frac{\text{drill pay}}{4} \times 48 = \$11,553.6 $$

for IDTs. Drill pay is higher than basic pay. I assume this is because drill pay is burdened by all the things you don’t get as an IMA: health benefits, BAH, BAS.

Annual Tour

Now, to calculate the annual tour (AT) we use the regular military pay tables. On the first page of the scale is your monthly military pay. First, find the pay that applies to you based on your rank and time in service. If you divide that number by 30, that gives you your daily pay. Multiply that number by the number of annual tour days you are required to do (14 in my case as a reservist) and you’ll have your before-tax annual tour pay.

$$ \frac{\$\,7221.22}{30} = \$\,240.71 \; \text{daily pay} $$

then $ \$ 240.71 \times 14 = \$ 3,369.90 $, which is appears to be exactly half of what I would get if I got IDT pay for the annual tour.

All together, this means \$15,000 a year in gross income from the reserves.

How do you value the retirement benefit?

To collect on retirement benefits, you have to attain age 60, not be entitled to receive military retired pay through any other provision of law and complete at least 20 years of qualifying uniformed service. So how much would I have to invest this year to have this benefit?

Should I make it to that age, on Tuesday, August 12, 2036, I will be 60 years old (21 years from now). Here I have to make some assumptions:

  • I retire as an O-6 in 6 years from now.
  • Officer pay rises with inflation
  • Discount rate of 6{aaa01f1184b23bc5204459599a780c2efd1a71f819cd2b338cab4b7a2f8e97d4}

So, 6 years from now O-6’s will be making a base salary of \$119,726.16. The defined benefit plan for DoD is 50{aaa01f1184b23bc5204459599a780c2efd1a71f819cd2b338cab4b7a2f8e97d4} highest salary or roughly \$60,000. In then-year dollars that would be \$71,479.65 a year. So, avoiding all fees, if I wanted to have enough cash to provide me with an annuity that paid \$71,479.65 a year in 2036, I would have to have \$1,191,327.50. So, if I wanted \$1,191,327.50 in 2036, how much would I have to save per year when I started the reserves? It is easy enough to compute the payment amount for a loan based on an interest rate and a constant payment schedule. In my case, this comes to \$20,138.61 a year that I would have to invest to get that benefit. You could see the math behind all this on wikipedia. Now, one might question the value of \$71,000 in 2036. If we experience several years of high inflation (which we will) that might not be worth much. For example, in current year dollars assuming a 4{aaa01f1184b23bc5204459599a780c2efd1a71f819cd2b338cab4b7a2f8e97d4} rate of inflation, the retirement benefit is only worth roughly \$31 thousand annually.

What about other benefits?

Now, you also have to compute the value of medical benefits, etc. Military discounts, commissary, etc, which are going to be highly dependent on the individual, but I would personally pay no more this year than $500 to get. (The medical benefits might be huge as might the post-9/11 GI bill.)

The other big benefit is career diversity and having a broader network and official connectivity to two government organizations. This alone might be the biggest benefit of the reserves if a member is very transparent and wise in how they use this opportunity.

So, in total, I would say that I make \$35,500/year in reserve benefits. What is the downside? I could be spending my reserve time on my main career which could lead to more salary in the right field. I could also be building a start-up with that time that also might pay off and doing something that might be closer to my passion. I could be investing in my faith, house, family or health. However, the fact I work for the government means that I can actually do a form of approved side work. Other jobs/consulting would be much more difficult and uncomfortable. I could certainly have much less stress if I gave this up.

Would love any thoughts, particularly those which correct errors in my thinking above.


  1. ADA568422.pdf
  2. AFI 36-2254
  3. MOAA National Guard/Reserve Retirement Benefits

CY2014 Quarter 1 Financial Review

Chrissy and I review our spending on a quarterly basis. Updating every 90 days isn’t too long to correct mistakes and remember purchases, but it also allows for the busy multi-week sprints that life presents us. While we have used every financial management program available, I’ve found the most straightforward and flexible solution is to download historical transactions into Excel where I can assign categories and do the type of analysis you can see below. This works for me because I have complete control. All the other solutions I used (MS Money, Quicken, Mint, GNU Wallet) introduce errors that have required lots of time to fix (or that can’t be fixed), but more importantly they constrain me to their interface and I got used to exporting information into tools that could flexibly answer my questions.

My basic workflow is to download statements from all our bank accounts and credit cards in put them all into one spreadsheet, where I ensure a consistent list of categories. I can do this quickly by filtering and sorting as most of our expenses are cyclical. Once everything is in the right format, I use lots of Excel SUMIF and SUMIFS functions to produce reports.

My purpose of doing a financial review is intended to accomplish the following:

  • Quality check (Are we getting paid the right amounts? Any incorrect expenses?)
  • Spending feedback (Are we overpaying in any categories? Anything we need to reign in?)
  • Tax Production

While my tax production and quality check was very helpful to me, I wanted to share the results of the spend analysis in case my reports might be useful to others.

Spending feedback

In summary, we had a small rise in our overall Grocery and Dining out categories, but the major cost drivers were:

  • Ellie’s 12 cavities were very expensive (no dental insurance)
  • We bought a new espresso machine (major purchase for us)
  • We bought a new car
  • We went crazy on clothes
  • Committed (again) to Army Navy Country Club


Where are we spending?

This doesn’t have a real effect on our spending, but I thought this was interesting. We don’t have saving/investments in here, this is just “spending”. I treated stuff like insurance, taxes, medical, fees, haircuts, etc as “cost of life” — things I feel we can’t avoid and don’t really have discretion in spending. Some other stuff that might fit this category (power bill) gets lumped into household (as does home maintenance and mortgage). I would love to do some more analysis and compare our spending to this article.


Daily Feedback

The plot below has categories on the Y-axis and days on the bottom. Intensity of color is the spend amount. I used matlab to produce this plot. I like it because the colormap used filters everything in way that comes out like a log scale — and that tells me what is a big deal and what is noise. The interesting dynamic is the frequency/magnitude trade that happens with spending dynamics: medical is in seldom/big chunks while grocery expenses are a constant but smaller expense.


You can see that our daily spending has a huge variance: The spending had a standard deviation that was twice our average spending — big purchases had a pronounced effect. I explore four levels of spending: discretionary (dining out), some and limited discretion (haircuts, medical) and non-discretionary (mortgage, tax) at the bottom.


Weekly Feedback

Click on the below to see full size


So how much can we control this?

If I break down spending into four categories:

  • Committed — We have to pay it (i.e. Mortgage)
  • Limited Discretion — We can commit extra time to reduce it (i.e. Home and Car Maintenance)
  • Some Discretion — We can make choices to decrease our quality of purchase (i.e. Groceries)
  • Total Discretion — We can do without this if we have to (i.e. Dining Out/New Clothes)

It turns out that a third of our expenses are committed where about a quarter each apply to limited and some discretion. Roughly 20{aaa01f1184b23bc5204459599a780c2efd1a71f819cd2b338cab4b7a2f8e97d4} of our expenses are totally discretionary and 70{aaa01f1184b23bc5204459599a780c2efd1a71f819cd2b338cab4b7a2f8e97d4} of our expenses could be changed if we had to. The takeaway for me is to focus on eliminating the stuff we pay for but don’t enjoy (fees) and the things that don’t bring joy/reward for their cost.

OFX for USAA via Ruby

My wife and I have been through roughly 10-15 different budget/financial tracking systems. We started with every penny in MS Money, used several different spreadsheets, spent several years in Mint and have pretty much dropped all of that for a top-down strategy that has us budgeting savings, non-discretionary spending, and a rainy day buffer and arriving at a fixed weekly budget for groceries, clothes, snacks, eating out and random household supplies. We use a debit card for this, and transfer the allotted amount every Thursday into the daily spending account. The problem is that we started pushing money into the account whenever it runs low, and we end up losing our focus and even the ability to track how much we spend in a given week. In an audit of last year’s spending, it was surprising to see that we were routinely 100{aaa01f1184b23bc5204459599a780c2efd1a71f819cd2b338cab4b7a2f8e97d4} over our budget when we looked at other spending sources.

Since I code web applications, I decided to play with bringing in some of the data we create, both household and financial to ultimately create a personal dashboard for our family. In doing so, we aren’t locked into any one system and we can create something custom that works for us. This way, we can track our fitness, finances, journal and home systems all in one place and own the data and experience. One lesson learned is that our tracking systems need to be on autopilot as our different interests surge. A fragile system doesn’t work. Our needs will vary, but we want any tracking system to be able to produce a report on request.

While fun and useful, this takes familiarity with some new protocols (OXF for finance and LUUP for home automation). On a plane flight to Las Vegas, I was able to get OFX to successfully connect to USAA. First I had to set a module with USAA’s specifics:

With this in place, I can generate a valid OFX request:

This request passes all the assertions designed to test for a valid signon response:

def verify_usaa_signon_response(response_document)
        signon_message = response_document.message_sets[0]
        assert signon_message.kind_of?(OFX::SignonMessageSet)
        assert_equal(1, signon_message.responses.length)

        signon_response = signon_message.responses[0]
        assert signon_response.kind_of?(OFX::SignonResponse)
        assert_not_equal(nil, signon_response.status)
        assert signon_response.status.kind_of?(OFX::Information)
        assert signon_response.status.kind_of?(OFX::Success)
        assert_equal(0, signon_response.status.code)
        assert_equal(:information, signon_response.status.severity)
        assert_not_equal(nil, signon_response.status.message)
        assert_equal(nil, signon_response.user_key)
        assert_equal('ENG', signon_response.language)
        #assert_not_equal(nil, signon_response.date_of_last_profile_update)
        #assert_not_equal(nil, signon_response.date_of_last_account_update)
        assert_not_equal(nil, signon_response.financial_institution_identification)
        assert_equal('USAA', signon_response.financial_institution_identification.organization)
        assert_equal('24591', signon_response.financial_institution_identification.financial_institution_identifier)
        assert_equal(nil, signon_response.session_cookie)

One of the difficult parts was to determine the required length of my account number in the absence of documentation. It took some experimentation to find out that USAA wants exactly nine digits for the username (member number) and ten digits for an account number. Instead of making code that robustly input padded zeros (through sprintf or similiar), I just changed the input values.

I also noticed that USAA did have


, but did not have the available balance fields that the gem expected. In any case, I can now get transactions and full access to my bank programmatically, which is pretty cool.


TelexFree — A quick business case assessment

Ever wonder why fund managers can’t beat the S&P 500? ‘Cause they’re sheep — and the sheep get slaughtered. I been in the business since ’69. Most of these high paid MBAs from Harvard never make it. You need a system, discipline, good people, no deal junkies, no toreadores, the deal flow burns most people out by 35. Give me PSHs — poor, smart and hungry. And no feelings. You don’t win ’em all, you don’t love ’em all, you keep on fighting . . . and if you need a friend, get a dog . . . it’s trench warfare out there sport and in here too. — Gordon Gecko

I built a much better model with more realistic constraints available here

Outside of generating new information or product, risk, diversity and time horizon are only variables I’m convinced an investor can control. This means invest broadly over long time horizons and keep taxes and expenses low. If you want outsized returns, you must take on more risk or make something people want to buy. Most wealth is created by businesses making real products, but wealth can still accumulate from appreciating assets (real estate, land, gold, internet domain names, etc). However, both these methods take a lot of time and effort. Can you make a lot more quickly through selling VOIP services, posting internet ads and joining a Brazilian-focused multi-level marketing (MLM) club called TelexFree?

Continue reading “TelexFree — A quick business case assessment”