US Battleship Fuel Usage


Quote of the Day

Y2K was 21 years ago. Looking back, I think the only thing we learned is that if a bunch people work really hard to stop a problem from happening, lots of other people will assume it was never really a problem.

— Steve Lieber. I have seen many people work hard to ensure problems would never occur. This effort was never appreciated. I have also watched people who created problems win awards for fixing the problems they created. Very strange organizational behavior.


Figure 1: USS North Carolina, my choice for the most beautiful of the US battleshps. (Wikipedia)

Figure 1: USS North Carolina, my choice for the most beautiful of the US battleships. (Wikipedia)

In this post, I will examine the fuel consumption of the three most modern battleship classes that the US deployed during WW2:  North Carolina,  South Dakota, and Iowa. The data is scraped from the Hyperwar website, which is one of my favorite targets for data extraction. In this case, the Hyperwar page contains a set of tables from the US Navy document FTP 218: War Service Fuel Consumption of US Navy Surface Vessels.

The fuel consumption (gallons/hr ) for a ship is a highly non-linear function of speed (knots), as my graphs will show. I also estimate each ship's running time as a function of speed using their rated fuel capacity, which is called radius oil that is the sum of the fuel oil and diesel capacities.

For those who like to follow along, my Excel workbook is available here.


US Battleship Classes

I first look at the different battleship classes the US deployed during WW2. Fortunately, a table in Wikipedia makes this data easy to access. Table 1 shows the ten battleship classes that the US deployed during WW2, along with the main battery diameter and the number of main battery guns. The battleship classes are shown in the order they were deployed.

Table 1: US Battleship Classes Deployed During WW2.

US WW2 Battleships By Name

There were 18 battleships in US Navy service on the morning of 7-Dec-1941. All were WW1-era ships, except for the 2 North Carolina class ships that were just entering service. After Pearl Harbor, 8 of these ships were damaged with 6 returned to service after months of repair and refit. The battleships in service immediately after Pearl Harbor bore the brunt of the fighting, both in the Atlantic and Pacific, until newer ships were completed.

Table 2: US WW2 Battleships By Name.


Scraping the data from the Hyperwar website was straightforward using Power Query because all the data was contained in well-structured tables. Figure 2 shows the fuel consumption of the USS South Dakota, USS North Carolina, and USS Iowa versus speed in knots. Note how their fuel consumption was similar at low speeds but diverged as their speed increased. USS North Carolina, in particular, was comparatively inefficient. The USS Iowa was quite efficient, with its fuel consumption rate at 29 knots roughly the same as the USS North Carolina at 25 knots.

Figure 2: Battleship Fuel Consumption Versus Speed.

Figure 2: Battleship Fuel Consumption Versus Speed.

Knowing a ship's fuel consumption as a function of speed and its fuel capacity, we can compute its sailing time before running out of fuel (Table 3). Ships normally would refuel their tanks regularly to ensure that they always had enough fuel to handle foreseeable needs (link). When they didn't, problems occurred (see discussion of Bismarck's fuel issues).

Table 3: Battleship Running Time Versus Speed.


It is hard to imagine ships that consume 9000+ gallons of fuel per hour. It is also impressive how fuel-efficient the USS Iowa as compared to the earlier classes.

We are fortunate that the US was able to to keep 9 battleships as museums ships:

  1. Alabama
  2. Arizona
  3. Iowa
  4. Massachusetts
  5. Missouri
  6. New Jersey
  7. North Carolina
  8. Texas
  9. Wisconsin

I hope to see them all in the next few years.

Posted in History Through Spreadsheets, Military History, Naval History | 3 Comments

50 Destroyer Pre-War Base Deal


Quote of the Day

Vision is not necessarily enough; it must be combined with venture. It is not enough to stare up the steps; we must step up the steps.

Vaclav Havel


Figure 1: Wickes-Class Destroyer.

Figure 1: Wickes-Class Destroyer.

This post is going to look at the Destroyers for Bases deal between the US and UK. The bargain was an executive agreement announced on 2-Sep-1940 to trade 50 WW1-era US destroyers to the UK for US basing rights in the Caribbean, Bermuda, and Newfoundland. I have seen the destroyers described as obsolete, which seemed odd for ~20-year-old destroyers that nominally have 30 year lifetime (typical for most US Navy ships).

Alas, age is not the only factor in determining the performance of a ship. These destroyers were designed and built under wartime conditions by a navy that was still learning the ropes of modern naval warfare. As such, their design and outfitting were probably not appropriate for service as WW2 North Atlantic convoy escorts battling Type VII U-boats. Performance-wise, the ships were "wet" and lacked the maneuverability, weapon systems, and sensors required of a WW2 anti-submarine vessel. To a large extent, this is to be expected because they were designed for a different time with different threats.

Corrosion was also another major issue with these ships. Most of these destroyers had been put into the US Navy's Reserve Fleet,  which is often called the "Mothball Fleet." Ideally, ships in the Reserve Fleet are treated to prevent corrosion. However, there were signs that these ships were not properly mothballed and many suffered from severe corrosion issues. These issues had to be resolved before the ships could go back on active duty. For more details on these issues, see this article.

To be fair, many of these ships were refit and used effectively by the US Navy in WW2. However, the UK was facing an existential threat from U-boats and they needed convoy escorts in a hurry. The time required to repair and refit the ships would be an obstacle to their defense of the UK. The following quote from Winston Churchill in a cable to Roosevelt in late 1940 sums up his opinion of the ships and their issues.

We have so far only been able to bring a few of your fifty destroyers into action on account of the many defects which they naturally develop when exposed to Atlantic weather after having been laid up so long.

The deal was important because it provided President Roosevelt a way to help the UK without raising the ire of the isolationist elements in the US. Once it was clear that the destroyers had issues, President Roosevelt transferred 10 Lake Class Coast Guard Cutters to the Royal Navy as a gesture of good faith. The Lake Class ships were 10 years newer and their design was more suited to ASW operations.

The rest of this post will discuss the 50 destroyers in the deal along with the other ships of the same classes. This is a tale of two nations, US and UK, trying to make use of whatever assets were available to give themselves time to build more modern ships.

For those who like to follow along, my Excel workbook is available here. All of the data is from Wikipedia.


Figure M: Commissioning Dates of US Battleships at the Start of WW2.

Figure 2: Commissioning Dates of US Battleships at the Start of WW2.

The 50 destroyers were built by the US during a 273 destroyer building program that started during WW1 (1916) and finished in 1922. By the time of WW2, all but ~30 of these ships ended up in Reserve Fleet.

While these destroyers are frequently described as old, understand that much of the early WW2 naval fighting was done using WW1-era ships. For example, seven out of nine US battleships at the start of WW2 were built during or shortly after WW1 (Figure 2). Of these old battleships, two were permanently lost during the attack on Pearl Harbor (USS Oklahoma and USS Arizona).

The destroyers traded to the UK were from three classes: Caldwell, Clemson, and Wickes. The Royal Navy referred to these ships as the Town Class. Table 1 shows that most of the ships of these classes served in WW2.

Table 1: Total Number of Caldwell, Clemson, Wickes-class Destroyers built.

As is shown in Table 2, the Clemson, Caldwell, and Wickes-class ships were very similar in terms of their general characteristics.

Table 2: Key Characteristics of Deal Destroyers.

I looked at the range of Royal Navy ship displacements for classes launched after 1935 (Table 3). The Caldwell, Clemson, and Wickes-classes are displacement-wise most similar in displacement to the Black Swan sloop or G-class Destroyer-classes.

Table 3: Displacement Range of Royal Navy Classes Launched Post-1935.


Notable Ships of the Clemson and Wickes Classes

Some of these ships did give notable service:

  • USS Ward (Wickes-Class Destroyer)
    Fired the first shots against a Japanese midget submarine entering Pearl Harbor just before the attack.
  • USS Reuben James (Clemson-Class Destroyer)
    The first US warship sunk by hostile action in the European theater during WW2 by the Germans. The sinking occurred on 31-October-1941, less than two months before the Pearl Harbor attack.
  • USS Buchanan (Wickes-Class Destroyer), renamed HMS Campbeltown
    The UK turned this ship into a surface running torpedo during the St. Nazaire Raid. The use of a surface ship as a torpedo reminds me of the boat African Queen, which starred in the movie of the same name.

Survival in US and UK Service

Table 4 shows the number of losses and survivors of these three classes, with about 16% of the ships being lost for all causes during WW2. I defined losses to include ships that ran aground or ended up being used as target ships;

Table 4: WW2 Survivors and Losses In the Destroyer Deal Classes.

Table 5 shows the losses of the three classes while in UK service (or those countries who the UK transferred them to), which ended up being about 20% of the ships were lost.

Table 5: Survivors and Losses in UK service.

While the 50 destroyers were traded to the UK, a number of them ended up in Canadian and USSR service (Table 6).

Table 6: Country whose Navy the Destroyers Ended Up.


The ships were not the best, but they were all President Roosevelt could provide at the time and still ensure his re-election in 1940. And this deal provided a small start on what would eventually become the Atlantic Charter, a cornerstone of US and UK cooperation during WW2.

Posted in Excel, History Through Spreadsheets, Military History, Naval History | 1 Comment

BB Ballistic Coefficients


Quote of the Day

A serious problem in planning against American doctrine is that the Americans do not read their manuals, nor do they feel any obligation to follow their doctrine.

— Soviet observation during the Cold War (link).

Figure 1: Steel BBs with copper or zinc jackets. (Wikipedia)

Figure 1: Steel BBs with copper or zinc jackets. (Wikipedia)

A number of years ago, I was asked by a father to assist him and his son with a science project that involved calculating the ballistic coefficient of a BB gun projectile. I provide this father-son duo with the required calculations (documented here) and the answer I obtained seemed reasonable.

Another reader has recently asked further questions on these calculations and I thought I would take a quick look at how the ballistic coefficient of various BBs varies from one manufacturer to another.

This was a quick task that involves scraping some BB data from a web page (here), parse a bit of HTML, and plotting the data. The task is straight forward and the calculations are documented in this Excel workbook (here). This task was a good exercise in using Power Query and Excel to extract data from a web page without tables.

Figure 2 shows the distribution of BBs documented on the previously mentioned web page. My earlier calculations, assuming nominal BB parameters, showed the ballistic coefficient to be ~0.0145, which is near the mode of the chart in Figure 2.

Figure 2: Distribution of BB Ballistic Coefficients from 80 different BBs.

Figure 2: Distribution of BB Ballistic Coefficients from 80 different BBs.

Posted in Ballistics, Excel | Leave a comment

Optimized Piecewise Linear Model Using Excel


Quote of the Day

Never respond too quickly or too well to a request that you do not want to see more of in the future.

— A truism of corporate life. This is my paraphrase of a statement by a coworker, Nick Priote, who often made very insightful observations on the corporate world vs academia.


Figure 1: Typical Li-Ion SOC vs Voltage Curve (Reference).

Figure 1: Typical Li-Ion SOC vs Voltage Curve for a specific discharge current (Reference).

I was recently asked to create a piecewise linear model for a rather complex battery discharge curve, which is a type of task that I have performed dozens of times. I was told to perform this task in Excel because that is the only computation tool that this customer uses. I normally do this task in R because I like the segmented package, however, Excel does a very good job with the task, especially if you use the Solver add-in to "tune" the model.

Figure 1 is the kind of curve I normally get from our test battery folks, which is the open-circuit battery voltage (VOC) vs State of Charge (SOC). The software folks, my customers for this modeling effort, typically know VOC and want to know SOC, so they care about the SOC vs VOC curve. This requires that you flip the abscissa and ordinate values, which is a minor transformation.

Because the actual battery data is proprietary, I will create a piecewise-linear approximation to a discharge curve I found on the web (Figure 1). For those who like to follow along, my workbook is available here.



Breakpoint (Knee Point)
The point on the curve where the approximation transitions from one line segment to another. Because we need continuity, the line segments must have the same value at the breakpoint.
Objective Function
This is the error measure that our algorithm will be minimizing.
Solver is an add-in for Excel. This is an unbelievably powerful tool that many folks do not even know exists. The Solver that is included in Excel is a reduced functionality version of a more extensive package from Frontline Systems. There is also an open-source version (link) under development.


There are a number of different optimization criteria we could apply to this problem, but for this exercise, I will be minimizing the maximum absolute error. You could easily change the algorithm to minimize the sum of the least square errors or a weighted error approach.

In the real battery problem (not shown in the workbook), I actually weighted the battery capacity errors more heavily when the battery state of charge was low versus when the state of charge was high. This is because people generally want more accuracy when their battery is nearly out than when it is full. I have done similar optimizations for aircraft fuel systems where customers are less concerned with accuracy when the tanks are full than when they are near empty. This is yet another manifestation of the utility curve from economics – stuff has increased value when you are short of it.



For this example, I am going to use five line segments to approximate a cubic function. For the actual problem that I worked, my measured battery curve looked nothing like a cubic function, but the process for determining the piecewise linear approximation is the same.

Here is the process I used (there are many possible alternatives):

  • Assume the State Of Charge (SOC) and battery voltages are known at the end points of battery voltage range: (VOC=2.8 V, SOC=0%), (VOC=3.5 V, SOC = 100%).
  • Pick three points (voltages) in the middle of the VOC range (2.8 V to 3.5 V).
  • Determine lines between each of these points
  • Setup a data table with a large number of points that encompass the whole VOC range.
  • Use the data table to compute a SOC value for every VOC point.
  • Determine the error between the model SOC value and measured SOC value (some interpolation will be needed for the measured SOC values).
  • Compute the maximum absolute error of the absolute differences between the model SOC values and measured SOC values.
  • Use Solver to select (VOC, SOC) values to minimize the absolute errors. The initial guess must be fairly close to the final values to ensure a good approximation.


See the workbook to see how the optimization is setup. One issue with this approach is that your starting values must be good for Solver to converge to a good solution.


Figure 2 shows the battery curve of Figure 1 and my interpolated results. Note that the curve of Figure 1 shows VOC vs SOC and Figure 2 shows SOC vs VOC, which is what the firmware folks I work with need. All I needed to do was flip the abscissa and ordinate values.


Generating piecewise linear approximations for non-linear curves is a very common task. In my career, about 90% of this work has involved batteries (always nonlinear) or sensors (frequently nonlinear).

One frequent problem is that the SOC vs VOC curve is frequently not a one-to-one function. This means that the VOC does not map to a unique SOC value. This makes SOC determination much more difficult, which means it will be a topic for another post.

Posted in Batteries, Excel | Leave a comment

US WW2 Torpedo Production Chart Using Power Query


Quote of the Day

Satisfaction is always available. It is just not always looked for. If, when you enter any experience, you enter with curiosity, respect and interest you will emerge enriched and with awareness you have been enriched. Awareness of enrichment is what satisfaction is.

— Ann Hastings, Author of The Mechanics of Reality: The Teachings of Vywamus Channeled.


Figure 1: US Production of Torpedoes in WW2. (Source)

Figure 1: US Production of Torpedoes in WW2. (Wikipedia)

During my readings on the Pacific War, I often see the chart shown in Figure 1. I decided to do a bit of digging and find the source data for this chart in the hope of making a version of this chart that is a bit clearer and easier to use.

My go-to source for munitions production data is the report Official Munitions Production of the United States By Months, July 1, 1940-August 31, 1945 (link). Fortunately, this report has a table of US torpedo production data, but simply plotting the data did not produce Figure 1. However, I was able to reproduce Figure 1 by doing a bit of data filtering.

This post documents my process for gathering the data, cleaning it, filtering it, and generating a cleaner and easier-to-use version of this chart. My workbook is here.


Figure 1 only shows the production of anti-surface ship torpedoes, which were grouped according to the platform they were designed to be launched from:

  • Submarine-Launched: Torpedo Mk 14, 18, and 23
  • Aircraft-Launched: Torpedo Mk 13
  • Surface Ship-Launched: Torpedo Mk 15

There were other torpedoes produced by the US during WW2 but they are not included in the chart. For example, the Torpedo Mk 24 ("FIDO") was an acoustic homing torpedo used primarily to destroy submarines.

Many older anti-surface ship torpedo versions were used during the war but were not being manufactured at that time. For example, the Bliss–Leavitt Mark 8 torpedo was used extensively by PT boats. Wikipedia has a table that summarizes the US torpedo versions.


Data Processing

The basic data extraction process was simple:

    • OCR the torpedo production table from the Offical Munitions Production document.
    • Put the OCR data into Excel
    • Import the data into Power Query
      • This is a complex table with a multi-level heading, so there was a fair amount of heading manipulation involved.
      • Convert the table to tidy format
      • Filter the data
        • The data contains some UK torpedoes that the US made but did not chart.
        • The data contains a small number of US Torpedo Mk 14 Mod 4, which I can find no record of it being deployed nor did it appear to be included in the chart.
    • Generate a pivot table of the data
    • Generate a chart of the pivot table.


Figure 2 shows my rendering of the data. I did change the chart format just a bit by adding production numbers to each column of the chart and removing the gridlines, which makes things a bit clearer for me.

Figure 2: US Torpedo Production in WW2.

Figure 2: US Torpedo Production in WW2.


My goal was to reproduce the chart shown in Figure 1, which I have done.

Posted in Excel, History Through Spreadsheets, Military History, Naval History | Leave a comment

18% of American's Can Determine 52 Senators?


Quote of the Day

The fastest way to teach someone nothing is to try telling them everything.

— Susanna L. Harris on Twitter, quoting her plumber explaining why her pipes were making noise. His quote is true for all science communication.


Figure 1: 26 States with the Least Population (

Figure 1: 26 States with the Least Population (

I was listening to a podcast this week where I heard James Carville state that "18% of American's can determine 52 senators." I thought this was an interesting quote that I could have the students I tutor verify using Excel and Power Query. All of the data is available online and the problem has a relatively short solution.

For those who like to follow along, my Excel workbook is here.


The only background information that you need to verify this statement is that every state has two senators. To ensure that I have complete population data, I used 2019 census data.



I will verify Carville's statement using the following process:

  • Download a list of US states with their populations into Power Query.
  • Insert the total population of the US  for computing population percentages (this number includes the District of Columbia and territories that do not have senators).
  • Convert the populations into percentages of the whole.
  • Sort the list in ascending order of population percentage
  • Look at the population percentage for the least populated 26 states = 52 senators


Figure 2 shows my spreadsheet results, which show that the 26 least populous states having 17.6% of the US population could determine control of the US Senate if they all voted the same way.

Figure 2: List of US States Sorted By Population.

Figure 2: List of US States Sorted By Population.

Posted in Civics Through Spreadsheets, Excel | 1 Comment

Willie Mosconi and How Pool is Like Life


Quote of the Day

A superior pilot uses his superior judgment to avoid situations which require the use of his superior skill.

Frank Borman, Astronaut

Figure 1: Willie Mosconi (Ron Moody, Find-A-Grave)

I was talking with my neighbors yesterday about motivating students. They are both teachers at a local community college and are finding it difficult to motivate their students over Zoom.  While I am not in education, many of the companies I have worked for have struggled to motivate staff (local and remote). My employers have occasionally brought in motivational speakers to try to fire up the workers, most of which were good but not great (Lou Holz was great).

As I thought about it, the best motivational speech I have ever heard was during a pool demonstration at North Hennepin Community College (NHCC), which is where I received my first two years of post-high school education. NHCC would bring in guest speakers on various topics and one day they brought in pool legend, Willie Mosconi (Figure 1).

Willie was not your typical speaker. He did not stand in a lecture hall and speak. Instead, he gave a demonstration at the pool table in the recreation area next to the cafeteria. I decided to watch the demonstration while I ate lunch. I am so glad I did.

Willie spoke with a cue in his hand at the pool table while setting up various trick shots, which were fun to watch. After a few trick shots, Willie then set up a typical game with a clean break and showed how he would approach clearing the table. During this demonstration, Willie talked about how every pool break was different and how you have to play the balls as they break. He talked about how you need to look at the whole table and decide which balls you are going to try to sink and in what order. You need to plan your shots so that you can make your goal step-by-step, not just take the easiest shots as they come up. Sinking an easy shot first may leave you with poor shots later. Plan where your cue ball will end up after you sink a shot so that you are set up properly for your next shot. If you have to make a tough shot that may fail, make sure you have a recovery plan – or at least leave the cue ball in a spot that makes it tougher for your opponent. Willie spent quite a bit of time discussing how to plan each shot so that each shot sets up the next shot.

As I thought about it, Willie was really talking about life. Everyone gets a different break. You need to look at your break and decide what your plan is to achieve your objective. Taking easy shots first may leave you with a bad setup for the rest of your life. Don't assume everything you try will necessarily work. Make sure you leave yourself options if things don't go as you plan.

That was a lot to learn from watching a guy shoot pool for an hour. I am now sufficiently motivated to start setting up a pool table in my garage (aka man cave).

Posted in Management, Personal | 4 Comments

Relative Cost of WW2 US Fighters


Quote of the Day

Vision is the bottleneck of talent. Most talent is wasted because people do not clearly know what they want. It’s not a lack of effort, but a lack of direction. There are many capable people in the world, but relatively few that focus on what matters.

James Clear

Figure 1: P-51 Mustang.

Figure 1: P-51 Mustang was a relatively low-cost aircraft (photo: Wikipedia).

A reader of this blog mentioned in a comment that cost might be a big reason for the US Army Air Corps (USAAC) switchover to the P-51 from P-38s and P-47s. I thought I would put together a quick report on the relative cost of the three main USAAC fighters. The cost of these fighters by year was available in the Army Air Forces Statistical Digest (Hyperwar Site). The approach to Extracting, Transforming, and Loading (ETL) the data are the same as I used to determine the on-hand numbers of aircraft (link). For those who are interested in the details, my workbook is available here.

Since the cost of these fighters reduced each year, I compared the cost of the P-47 and P-38 to the P-51 costs by year. Table 1 shows my results. Note that no costs were listed for the P-38 in 1945.

Table 1: Relative Unit Costs of the Major USAAC Fighters (P-51 Basis).
Type and Model 1942 1943 1944 1945
P-38 2.05 1.79 1.88
P-47 1.80 1.77 1.66 1.63
P-51 1.00 1.00 1.00 1.00

The P-38 unit cost was approximately twice the unit cost of the P-51. This makes intuitive sense because a P-38 looks a bit like two single-engine aircraft glued together. Note that the P-47, a single-engine aircraft, also was a very expensive aircraft relative to the P-51.

So cost could have been a serious issue when it comes to large deployments. Ultimately, cost should reflect the resources required to build something. For the same resources, you can build many more P-51s than either the P-38 or P-47. Since quantity has a quality all its own, the transition to the P-51 made sense. Also, it was arguably the better fighter.

I leave you with a photo of the p-38 (Figure 2). It sure was a beautiful aircraft.

Figure 2: P-38J (Wikipedia).

Figure 2: P-38J (Wikipedia).

Posted in Excel, History Through Spreadsheets | 2 Comments

Lake Water Temperature Report Using Power Query and FILTERXML


Quote of the Day

In times to come, when we are all gone, people not yet born will walk in the sunshine of their own days because of what women and men did at this hour to feed the sick, to heal and to comfort.

Jim Dwyer, journalist, writing about the Irish women who cared for others during the 1918 pandemic.


Eagle Lake

Figure 1: Satellite View of Eagle Lake. (Google Maps)

While COVID rages around the US, I have been staying isolated at my lake cabin in northern Minnesota.  Because of my love for data, I track various lake metrics such as height relative to sea level, clarity, and temperature.  I gather this information into a report that I send to the other folks who live on the lake. In this post, I show how I put together a chart of lake water temperature vs time using Excel and Power Query. I also demonstrate how to use the FILTERXML function to parse some rather messy data. I find myself using FILTERXML for data parsing more and more.

For those who like to follow along, you can download my workbook here.


Temperature Measurements

I measure the lake temperature at the end of my dock using a K-type thermocouple. The thermocouple is mounted 15 centimeters below the water surface. While I take the readings manually today, I am setting up an Arduino system to automate the temperature reading next year. I begin taking readings as soon as the ice is off the lake and stop taking readings when the lake is ice-covered. The lake is not ice-covered yet, but I decided to start putting my report together early this year.

Air Temperature Readings

Because my wife likes to see how air temperature and lake temperature are related, I also plot the air temperature as recorded by a nearby National Oceanic and Atmospheric Agency (NOAA) site in Hibbing, Minnesota.  They have an excellent web site that allows you to download all sorts of US weather data (link).

Parsing with FILTERXML

I have never cared for Excel's Text-to-Column function because it does not update automatically. I now tend to parse text using functions or FILTERXML. For this example, I will be using FILTERXML. Because you may not be familiar with it, I include an example of how it can parse an XML string in Figure 2. For general parsing, you often can convert a string to an XML string using the substitute command and then apply FILTERXML to parse the modified string. For more details, see the Chandoo blog for a great example.


Figure 2: FILTERXML Examples.


Data Processing

I recorded my lake temperature data on Google Keep over time. When it was time to report, I simply pasted the data into the LakeData tab of the Excel Workbook. I then used FILTERXML to parse the data. Similarly, I downloaded the air temperature data from the NOAA website, converted from PDF to CSV using Tabula, and pasted the data into the NOAA tab of the workbook. Both raw tables were loaded into Power Query for general cleaning (e.g. converting strings to dates, etc).  The clean data is then charted in Figure 3.

I should mention that the downloadable data from NOAA is always two days behind.


Figure 3 shows the plot that I will use in my lake metrics report for lake and air temperature. The light yellow area chart shows the range of daily temperatures. The purple diamonds show the lake temperature data. The chart does show that the lake temperature and air temperature tend to track most of the year, but diverge as winter arrives because the lake does not cool off as quickly as the air.

Figure 3: Lake Water and Air Temperature Data.

Figure 3: Lake Water and Air Temperature Data.


This workbook is a good example of how to gather data, parse it, clean it, merge it, and plot sensor data. It shows how to parse the data using FILTERXML that updates automatically as more data is collected.

Posted in Cabin, Excel | 1 Comment

US Army Air Corp Fighters on Hand During WW2


Quote of the Day

Lack of confidence kills more dreams than lack of ability. Talent matters—especially at elite levels—but people talk themselves out of giving their best effort long before talent becomes the limiting factor. You're capable of more than you know. Don't be your own bottleneck.

James Clear


Figure 1: Thunderbolt P47 with the French Air Force (Wikipedia).

Many years ago, at the start of my career, I worked with an excellent safety engineer who had served in WW2 as a fighter pilot in Europe with the US Army Air Corps (USAAC). You could tell that flying was the love of his life. Though we were working on naval weapons systems, our lunchtime talks often focused on his experiences flying aircraft during the war. His war service began in a P-47 (Figure 1) and his unit later transitioned to the P‑51 (Figure 2).

Figure 2: P-51 Mustang.

Figure 2: P-51 Mustang (Wikipedia).

Most of his flying was over Europe doing bomber escort. While he generally had good things to say about both aircraft, most of his stories were about the P-51. One day I asked him if he had a preference between the P‑47 and P‑51. He answered with no delay and I found his response so interesting that I wrote it down in my collection of quotes.

I wanted to be flying a P‑47 if someone was going to be shooting at me because there was no coolant to leak from its radial engine if I was hit. If I was just going flying, then I wanted to be flying a  P‑51. The P‑47 could dive very fast, but it did not climb well.

I started to wonder about units transitioning from one aircraft to another during the war. I decided to look at the Army Air Forces Statistical Digest (Hyperwar Site) to see if there was any information about fighter inventories during WW2. It turns out this document has several tables of aircraft inventory throughout the war. I used Power Query to Extract, Transform, and Load (ETL) the data into a quick report.

For those who like to follow along, my Excel Workbook can be downloaded here.


Excluding some obsolete fighters, the P-39 and P-40 were the main USAAC fighters on December 7, 1941. While both of these fighters had their strengths, they both had weaknesses relative to fighters like the Luftwaffes's BF-109 and Imperial Japanese Navy's A6M Zero.  As quickly as possible, the P-39 and P-40 were replaced with the P-38, P47, and P-51. My workbook will look at this transition and how quickly it occurred.


My analysis method is straightforward:

  • Use Power Query to directly download the download the data from the Hyperwar web site.
  • Because the data is in multiple tables, develop a function that could process each table the same way.
  • Apply the cleaning function to each table and consolidate the data.
  • Plot the data.

Figure 3 shows the USAAC's on-hand first-line fighters during WW2. We can make some observations about how the on-hand fighter inventory varied:

  • The P-47 numbers ramped up starting in mid-1942 and pretty much flattened out by mid-1944.
  • The P-51 numbers started to ramp up in the second quarter of 1942 and continued to ramp until just before Victory of Japan Day (VJ-Day).
  • P-39 and P-40 numbers began to decrease in early-1944. The P-39 did provide good service with the Soviets and the P-40 did well on ground support in the Mediterranean and North Africa.
Figure 1: USAAC Fighter Inventory During WW2.

Figure 3: USAAC Fighter Inventory During WW2.


The on-hand inventory data shows that the P-47 was the most numerous USAAC fighter through most of WW2. Its numbers stayed roughly constant after April-1944. The P-51 inventory ramp-up started later and was more gradual than that of the P-47, but they ended the war with similar on-hand numbers.

Posted in Excel, History Through Spreadsheets, Military History | 3 Comments