The Threadless Nut

Verizon recently added a “Friends & Family” thing to their mobile plans where you can pick 10 numbers that won’t count against your minutes. The website provides details about your calls, but they don’t provide a good tool to find the 10 people you call the most. Hmm, what to do?

OpenOffice to the rescue! Since Verizon makes call details available as a CSV download, they can be easily sucked into a spreadsheet where you can do all sorts of unholy data manipulations.

Here’s the, somewhat ungraceful, procedure I worked out to find out who we call the most. I’m sure things could be automated more, but I’m also pretty sure it would take me a lot longer to do that than this took. All this stuff should work in Excel too, but no promises.

1. Login to your Verizon account, Go to My Bill then Bill Details.
2. Select the date you want and the number you want.
3. Hit the Download to Spreadsheet link to download your call data into OpenOffice, you’ll need to repeat this for each number on your account separately. The default import options should work fine.
4. Get all the data pasted into 1 spreadsheet page. I did a page per month, but you could mash it all together too.
5. Turn on Data->Filter->Autofilter.
6. Filter out the calls to other Verizon numbers that don’t count towards minutes.
   1. Click the pulldown that appeared at the top of the Usage Type column.
   2. Select each type of in network call. The are listed as M2MAllow, M2M Allow,CallWait, IN Allow, or IN Allow,CallWait. Depending on the date of your statement. You may also want to filter out nights and weekends (N&W), but I left that in since I really just wanted to know who we call the most regardless of time.
   3. Select all the filtered rows and delete them, repeat this for each type. Don’t delete the header.
   4. Select All in the filter to get back to the remaining data.
7. Now, here’s the good stuff, select the entire sheet and go to Data->Subtotals.
8. Select Number for group by, Minutes to calculate subtotals for, and Sum as the operation. Click Okee Dokee.
9. Violin! The little elves will group all the unique numbers and place a subtotal of the minutes under each group. If you click the little 2 on the far upper right, you can collapse it so you only see the subtotal lines.

Just go and pick out your top 10 from there, easy peasy, well, easier peasier.

I’ve recently moved our photo hosting operation over to SmugMug . Up until recently I’ve been using a local webserver with a statically generated BINS photo album. That combination worked quite well, but scalability was a bit lacking, and there’s no tagging facility. Not to mention that I need to leave my machine on all the time. SmugMug addresses those issues quite well. It also essentially provides an off-site backup of your photos and videos (with the $60/yr plan).

I began using F-Spot to manage the photo uploads from the camera, and from our previous archive, and to do all the tagging. Then F-Spot can directly upload to Smugmug. Everything works pretty well except for the tagging. F-spot stores the tags in the “Subject” field of the EXIF data, while SmugMug looks for them in the “Keywords” field. As a result the photos on SmugMug had no tags. Sad Clown.

Once the problem was discovered, with some help from SmugMug’s forum, a little Googling revealed an almost fix in this blog entry. There was a slight problem where it was munging multiple tags into a single multi-word tag, but that was easily fixed via the -b flag, or -sep on newer versions of exiftool. The version in Ubuntu 8.10 still uses -b but try -sep if that doesn’t work on yours.

So the following command successfully copies the Subject fields contents over top of the (empty) Keywords field. Since the path restricts it to a single month, it doesn’t take too long, and everything is peachy afterwards.

exiftool -overwrite_original -r -P -b -“IPTC:Keywords<XMP:subject”  ~/Photos/2009/02/

Incidentally if you decide to sign up for SmugMug, you can get a $5 discount with my referral code: Wo6AztZyF7W7I

Yes, I get a kickback too.