Precision Computing

Recently, Jeffrey wrote a post, “Blog your initial PowerShell  experiences.” I thought, “Even better, blog your automation!” So much of what makes people fall in love with PowerShell comes from the little problems it helps you solve. The more of those techniques we can help people learn, the better.

That post turned into “Break your Writer’s Block” – which is good, because there is already a good discussion going about blogging your automation here and here. Make sure to read the comments (if you haven’t already,) as they are an excellent source of reader contribution.

One thing I found myself doing the other day was trying to calculate how much I had contributed to my 401(k) plan. The web interface was annoying, but they did offer an option to download a .QIF file that contained my transaction history.

The.QIF file format uses newlines to separate elements of a record, and the carat (^) to separate records – like this:

!Type:Invst
D01/13/2006
NBuy
Y<Name of Mutual Fund>
I<Something>
Q<Shares>
U<Something>
T<Transaction Amount>
MContribution                 
^
D01/31/2006
NBuy
Y<Name of Mutual Fund>
I<Something>
Q<Shares>
U<Something>
T<Transaction Amount>
MExchange In                  
^
(...)
^
D12/29/2006
NBuy
Y<Name of Mutual Fund>
I<Something>
Q<Shares>
U<Something>
T<Transaction Amount>
MContribution                 

Each field is prefixed by a letter (D, N, Y, I, Q, U, T, M) to signify that field’s purpose. Normally, this task could just be accomplished by selecting all of the “T” lines (Transaction Amount,) and then adding them up. However, not all of the records represent contributions (M = “Contribution”) to the plan. A few of them represent exchanges (M = “Exchange In”, M = “Exchange Out”) between plans, which would result in double-counting those contributions.

So what’s a scripter to do? One line, that’s what:

Get-Content c:\temp\401.txt -Delimiter "^" | Parse-TextObject -Delimiter "`n" | ? { $_.Property9 -like "*MContribution*" } | % { $_.Property8.Replace("T","") } | Measure-Object –Sum

First, we read all of the text from the file as a character stream, using the -Delimiter option to break it into records by splitting on the “^” character. For each of those records, we pass it into the Parse-TextObject script (telling it that fields are delimited by a newline character.) Then, we pass along all records where the 9th property was a contribution, remove the “T” in transaction amount field, and then calculate the sum.

It turns out that the QIF file didn’t contain the granularity I needed (as it rolled my contributions and the company match on contributions together,) but the exercise was helpful in any case.