Monthly Archives: November 2018

In this post, I will use Power Query )PQ) to gather (aka web scrape) the US Medal of Honor (MOH) recipient names, rank, service, and conflict from a website called the Congressional Medal of Honor Society (CMOHS), which states that there have been  3505 MOH recipients. They have a beautiful website with the records stored on 140 ages with 25 names per page and one page with 5 names. Normally, I would use Python to scrape a large number of web pages but I am trying to use PQ more because my customers all have Excel. My approach as a contractor is always to work within the existing infrastructure of my customers. A copy of my workbook is available here. Continue reading →

I have been tutoring math at the local library and using Excel as a vehicle for encouraging people to explore everyday math. While at the library, I heard a young man ask "How many Friday the 13ths are in a year?" Since I am always looking for computational examples, I showed him how to use Excel to find the answer for himself. This post shows how I taught him to solve the problem. The solution turned out to be a good example of using Excel's date and array capabilities. Continue reading →

I recently finished a job where the customer wanted an Excel dashboard that displayed metrics for test case completion and various success metrics. This dashboard contained many control shapes that I wanted to be centered in cells. I do not like to manually adjust objects so I googled for a VBA routine that would center a shape. I soon found a nice piece of code by HipGecko on the Mr. Excel forum that centered pictures in the active cell. A simple modification of this code allows it to center shapes, an object type that includes pictures and controls. Continue reading →