Monthly Archives: February 2020

Web Scraping Sailboat Reviews Using Power Query

Posted on 25-February-2020 by mathscinotes

While it is true that I worked on US Navy contracts for twelve years and spent some time on ships testing new underwater vehicles, I know very little about recreational boating. However, I have always been fascinated by sailing, though this fascination has been limited to reading books about the Age of Sail. Continue reading →

Posted in Excel, Personal | Comments Off on Web Scraping Sailboat Reviews Using Power Query

Visualizing House of Representative Data Using Power Query

Posted on 18-February-2020 by mathscinotes

I have been following certain bills through the US House of Representatives and wanted to know how the voting varied by US regions and political parties. I also want to generate tables that show how my state's representatives vote (Figure 1). Fortunately, the votes are documented online and Power Query was able to easily grab and process the data. Continue reading →

Posted in Civics Through Spreadsheets, Excel | Comments Off on Visualizing House of Representative Data Using Power Query

Power Query Regular Expression Hack

Posted on 12-February-2020 by mathscinotes

I use Python, R, and Excel every day in the course of my work. Because many corporations are focused on the Microsoft Office suite of tools, many businesses require that I use Excel/Power Query so that they can work with the tools I develop after I am done. Fortunately, I really enjoy using Power Query, but I find it irritating that it does not support regular expressions. I must admit that Power Query's standard functions can do a good job of extracting strings, but the process is a bit tedious.  However, I have a large library of regular expressions for extracting email addresses, phone numbers, social security numbers, and the like that would be efficient for me to use. Continue reading →

Posted in Excel | 3 Comments

US Navy WW2 Aviation Statistics Cleanup Using Power Query

Posted on 4-February-2020 by mathscinotes

was reading a forum post on fighter kill ratios during WW2 and decide to compute some  Imperial Japanese Navy (IJN) vs  US Navy (USN) ratios for myself. I should point out that these ratios are generally viewed as inflated because of the difficulty of confirming downed aircraft. However, the inflated numbers continue to be quoted. The published reports state that the F6F Hellcat had the best kill ratio of the USN/Marine fighter at 19-to-1, followed by the F4U Corsair at 11-to-1, and the F4F Wildcat at 7-to-1. Continue reading →

Posted in History Through Spreadsheets, Naval History | 2 Comments