As I’ve mentioned previously, I tend to use Powershell for all manner of random things. This time around I wanted to use it to avoid having to upload a csv to google drive or opening up my other laptop that has Excel on it, just to get some numbers.

I’m self-employed, so I have to regularly do my personal tax return. My – extremely inefficient – process involves leaving it until the last minute, then trawling through my online bank account and downloading each month’s statement in csv format and digging through these to find the numbers I need to fill out the various documents.

Naturally, I’d prefer to do this automatically, ideally scripted. So I did!

Let’s assume you’ve got a CSV file called September.csv that you want to play with, perhaps with content a bit like this:

Date,Description,Amount
2016/09/13,Black Coffee,3
2016/09/12,Espresso,1.70
2016/09/11,Double Espresso,2
2016/09/10,Whiskey,8

Let’s read the csv file in as text:

Get-Content .\September.csv

Now let’s convert the content into a Powershell object by piping the file contents into another cmdlet:

Get-Content .\September.csv | ConvertFrom-Csv

Here’s where we can use the Measure-Object (or just measure) cmdlet to pull out various useful stats, such as Sum, Average, Maximum, and Minimum for a given property – which in this case maps to the “Amount” column in my csv file:

Get-Content .\September.csv | `
ConvertFrom-Csv | `
Measure-Object "Amount" -Average -Sum -Maximum -Minimum

This gives us the results:

Count    : 4
Average  : 3.675
Sum      : 14.7
Maximum  : 8
Minimum  : 1.7
Property : Amount

Awesome!

How about you want to filter that a bit? I want to know just how much I’ve spent on espressos, so I’m going to use Where-Object (where for short):

Get-Content .\September.csv | `
ConvertFrom-Csv | Where-Object {$_.Description.Contains("Espresso")} | `
Measure-Object "Amount" -Average -Sum -Maximum -Minimum

Which results in:

Count    : 2
Average  : 1.85
Sum      : 3.7
Maximum  : 2
Minimum  : 1.7
Property : Amount

Handy, huh? Another little one-liner to get you through the day.