Useful utilities for photographers

I’ve been shooting this year’s Christmas pictures with a brand new Nikon D7100 SLR (wonderful, wonderful camera btw but that is discussed in all the right places). Well it so happens that I had set the time incorrectly in the camera’s preferences and only noticed at the point of transferring the files to my computer. All file dates were offset by two months – i.e. they would show October 26 instead of December 26… Since the exif dates drive my entire folder structure via Breeze Systems’ Downloader Pro tags, that was going to be an issue…

I had to deal with the video files (*.mov) manually but there weren’t too many of those. As for the jpeg’s, a couple clever utilities later and all is well:

Step 1

E:\> jhead.exe -da2013:12:26-2013:10:26 *.jpg

Step 2

E:\> dirdate.exe CREATED= MODIFIED= exif *.jpg

‘hope this can help you out when needed.

Merry Christmas and a Happy New Year!

Posted in Tidbits | Leave a comment

Spreadsheet Design Standards

Now this is interesting. I remember searching a few years back for spreadsheet design patterns. That combination of keywords never led me to the Best Practice Modeling Standards upon which BPM – a company founded in 2002 by ex-Salomon Smith Barney investment bankers – provides its modeling products and services. The base version is apparently maintained by an independent Spreadsheet Standards Review Board:

Spreadsheet Standards Review Board Charter

  1. To promote the Best Practice Spreadsheet Modeling Standards to ensure that the Best Practice Spreadsheet Modeling Standards are recognized, used and accepted as the highest professional spreadsheet modeling standards in the world;
  2. To develop and maintain the Best Practice Spreadsheet Modeling Standards;
  3. To bring together global Spreadsheet modeling skills in order to develop and maintain the Best Practice Spreadsheet Modeling Standards;
  4. To facilitate and manage participation of interested parties and the general public in developing the Best Practice Spreadsheet Modeling Standards; and
  5. To evaluate proposals to add, delete or modify the Best Practice Spreadsheet Modeling Standards.
Posted in Data Management, Excel | Leave a comment

So you think you know Microsoft Excel?

Think again… I thought I knew my way around a fair share of Excel’s functionality but I’m not so sure anymore, having finally had a chance to play a bit with Microsoft’s new Excel add-ins: Data Explorer and GeoFlow. As a first test, I set out to create a map of some of my Outlook contacts – which took all of 5 minutes:

geoflow_test

This is barely scratching the surface and doesn’t even begin to address to power Excel has gained over the last couple years with the release of PowerPivot , its DAX language and PowerView.

What’s “a fair share of Excel’s functionality” anyway? All I can say is that with Excel as will all things, the more you learn, the more you realize how little you know…

Posted in Excel | Leave a comment

Rolling up multiple rows into a single row and column for SQL Server data

How many times have I needed to do this for reporting purposes? I remember coming up with some convoluted process that did the trick but was in no way as elegant as the solution suggested in this post (or the one in the comments).

Posted in Technology | Leave a comment

Data Quality & Data Profiling

You might find the two following posts interesting if you’re dealing with vast amounts of data:

Posted in Technology | Leave a comment

New Uses for Tables in Excel 2013 (Linked, Reverse Linked, Linkback)

Very interesting post by Marco Russo related to tables in Excel 2013 and their relationship to the PowerPivot data model.

Posted in Uncategorized | Leave a comment

Changing all pivot table data fields to sum

Ever needed to change a large number of pivot table data fields from Count to Sum? I used to have a macro just for that in my personal.xls file but my office workstation was upgraded a few weeks ago and I had forgotten to keep a copy of the code. I found the code again at vbaexpress.com and amended it slightly so that it applies to the currently selected pivot table only:

Sub ChangePivotTableFieldsToSum()

Dim pt As PivotTable

On Error Resume Next
Set pt = ActiveCell.PivotCell.PivotTable
On Error GoTo 0
If pt Is Nothing Then
    MsgBox “No PivotTable selected”, vbInformation, “Oops…”
    Exit Sub
End If

For Each pf In pt.DataFields
    If Not pf Is Nothing Then
        With pf
            .Function = xlSum
        End With
    End If
Next pf

End Sub

Posted in Excel | Leave a comment