## History Through Spreadsheets: Supreme Court Confirmations

Quote of the Day

You can no more win a war than you can win an earthquake.

Jeannette Rankin. This quote gets me thinking about recent history – every US president ought to think hard about this quote. There is a related quote from Lawrence Korb that I like which says "Wars don't solve problems, they determine who will solve the problems."

## Introduction

Figure 1: Supreme Court Confirmations By President. (Data Source)

I recently gave a seminar to my staff on using Excel with Power Query. As part of the seminar, I presented a number of web scraping examples that were well received, and I decided that some of you may appreciate them also.

When I was training myself on web scraping, I needed some free and interesting examples on which to practice. The most sophisticated work I have done involved scraping geographic data from the Wikipedia (example). For the seminar, I needed some examples that were simpler. Since I am interested in history and politics, I decided to focus on two broad categories:

• History: WW2 is a particularly rich area, but there are many others.
• Civics: Primarily fact-checking politicians – the amount of political lying lately is breathtaking.

Last night, I watched President Trump announce his nomination for the Supreme Court seat that was held by Antonin Scalia. While watching the announcement, I decided to grab some data on the number of Supreme Court confirmations by president and plot the data (Figure 1). There are some interesting facts to glean from this graph:

• George Washington got so many appointments because he was first and starting with an empty court – no one will ever be first again. The Supreme Court initially only had six members, but there was some churn during his administration. The number of justices did not settle down to nine until 1866. At one point, it was as large as ten.
• Franklin Roosevelt got so many appointments because he was president the longest. Even with all his appointments, he was not happy with the Supreme Court justices at the time – historians still talk about his attempts to "pack" the court by increasing its size.
• Four presidents had no Supreme Court appointments – Jimmy Carter was the last president to have no appointments.
• I would suspect we will see fewer appointments each presidential term because the justices are being nominated when they are relatively young and they serve until they are quite old. Presidents like to appoint young justices because it extends their legacy.

For those who are interested, my source file is here.

## US Manufacturing Employment Versus Time

Quote of the Day

Character is a diamond that scratches every other stone.

Figure 1: Total US Manufacturing Employment Since 1939. (Data Source: Bureau of Labor Statistics)

The only television news program that I watch is the PBS Newshour. I particularly like the discussions between Mark Shields, a reasonable liberal, and David Brooks, a reasonable conservative. On inauguration day (20-Jan-2017), they had an interesting discussion about the challenges the US faces and what can be done about them.

During the discussion, Mark Shields made a statement on US manufacturing employment that was both dramatic and easy to fact check. He stated that

In 1940 there were 137 million people in the Unites States of America –and ah, 132 million – and there were 600,000 more factory jobs than there are today. There were 8 million more factory jobs in this country when Jimmy Carter was president.

I decided to surf over to the US Bureau of Labor  Statistics (BLS) and see what the official statistics say. The BLS tracks manufacturing employment – I could not find "factory employment." Assuming that Shields was talking about manufacturing employment, I plotted the BLS data in Figure 1. Here is what I gleaned from these numbers:

• There are actually 1.4 M more manufacturing jobs at the end of 2016 then in 1940. So Shields is wrong on this point.
• Shield's overall point is that manufacturing employment has not tracked with population – he is absolutely correct about that. The US has roughly 320 million people today, which is nearly 2.5x the US population in 1940. If manufacturing employment had tracked with population, we would have 26.3 M people employed in manufacturing today rather instead of the 12.3 M we actually have.
• He is right about manufacturing employment peaking during the Carter years and the number of manufacturing jobs was nearly 8 M more than today.
• It is unclear to me how many manufacturing jobs were lost because companies decided to move overseas versus structural changes in our economy that reduced the need for manufacturing workers. For example, Fortune magazine is reporting that 88% of the US manufacturing jobs lost were due to automation and local factors, not international competition.

Figure 2: Bishman Tire Changer. (Source)

I am from a small town (Osseo, MN) where employment was divided between manufacturing and agriculture. The main manufacturing employer was a company called Bishman Manufacturing, which made an automobile tire changer (Figure 2). I remember when they closed shop in Osseo (1970s) and moved to South Dakota to find lower wage workers. A loss of jobs like that is devastating for a small town. Most of the workers refused to move, with many of the older workers taking low-pay jobs to try to bridge them to retirement.

If you wish to see the actual statement by Mark Shields, I have included a video link in Figure 2. For those who want to check my work, here is my source file.

 Figure 2: Mark Shields Statement Starts at 14:45 minutes into the Video.

Save

## Slide Rules of the Rocket Pioneers

Quote of the Day

Too often man handles life as he does the bad weather. He whiles away the time as he waits for it to stop.

Figure 1: Typical student slide rule from the 1960s. Mine was very similar. The minute calculators became available in the 1970s, I bought one – for about $150. That was a bunch of money for a high-school kid back then. (Source) I was watching a interview with Valerie Neal, Curator and Chair National Air and Space Museum, on CSPAN. The interview was focused on the history of rocket development in both the US and Soviet Union. Valerie was asked what was her favorite artifact at the National Air and Space Museum. She responded that she liked artifacts that were the personal items of the pioneers. In the case of space travel, she said that the slide rules of rocket pioneers Wernher von Braun and Sergei Korolev were her favorite artifacts. Both men used the same type of slide rule. As I looked closely at the slide rules (Figures 2 and 3), I realized they were the same brand – Nestler – as used by some engineers I knew as a boy. My slide rule was a Pickett, similar to that shown in Figure 1.  Figure 2: Slide Rule of von Braun. Figure 3: Slide Rule of Korolev. Unfortunately, the photos are not high enough resolution to read the specific model numbers of the slide rules in Figures 2 and 3. I have read in this document that von Braun preferred the 9 scale, Nestler 23R slide rule – which was also used by Einstein. Figure 4 shows a more detailed photo of this slide rule. Figure 4: Detailed Photo of Nestler 23 Slide Rule. (Source) For those who want more background on slide rules, here is an excellent document. Save Save Posted in History of Science and Technology | 2 Comments ## Futurama Quote on Largest Buggalo Ranch on Mars Quote of the Day A new idea comes suddenly and in a rather intuitive way. But intuition is nothing but the outcome of earlier intellectual experience. — Albert Einstein Figure 1: Buggalo Ranching on Mars' Western Hemisphere. (Source) I am a big fan of Futurama – its going off the air was as disappointing for me as discovering Firefly a few years ago and having only 14 episodes to watch. I love the fact Futurama often includes small bits of real math and science in its scripts. My all time favorite piece of math includes a blackboard showing an actual proof using group theory by Sweet Clyde in the episode "The Prisoner of Brenda." Today, I was watching the episode "Where the Buggalo Roam," which mentioned that the area of the Wong ranch encompassed the western hemisphere of Mars. Here is the exact quote:  Farnsworth: This is quite a large ranch you have. Mr. Wong: 17.9 billion acres. We own entire western hemisphere. [whispering] That the best hemisphere! Farnsworth: It's the same on Earth. I did the following calculation that confirmed that Futurama got the Mars hemispherical area calculation correct – these folks take their science and math seriously. Figure 2: Calculations Showing that Futurama Computed the Hemispherical Area of Mars Correctly. Posted in Humor | 2 Comments ## Effect of Earth's Curvature on Suspension Bridge Dimensions Quote of the Day Many people want to leave a better world for their children. I'm trying to leave better children for my world. — Carlos Slim ## Introduction Figure 1: Schematic of the Verrazano Narrows Bridge. I have received a number of questions recently on how the curvature of the Earth affects building construction. In general, the effects of the Earth's curvature are ignorable because most man-made construction is on too small of a scale to notice the effects of the Earth's curvature. One well documented exception is the Verrazano-Narrows bridge, whose design took into account that the bridge towers are 1 5/8 inch farther apart at the top than at the bottom. In this post, I will show how to compute this value. The calculations here are straightforward. My intent is to show that there are some structures that must take the Earth's curvature into account. There are two other examples that I know of: Stanford Linear Accelerator (Source), and Fermilab's neutrino communication experiments (Source). ## Analysis Figure 2 shows how to compute the 1 5/8 inch of additional separation based on the drawing in Figure 1. Figure 2: Calculations for the Additional Separation Between the Bridge Towers. ## Conclusion The Earth's curvature will only have significant effects on massive structures that are sensitive to small errors. With the Verrazano-Narrows bridge, we are talking about a structure with a size on the order of a 1000 feet and the effect of the Earth's curvature is ~1 inch. Save Save Save Posted in Construction, General Science | Leave a comment ## Larsen Ice Shelf and Potential Sea Level Rise Quote of the Day It is better to fail in originality than to succeed in imitation. Figure 1: Larsen C Ice Shelf. (Source) I just read an article about a large iceberg that will likely form in 2017 when a 5,000 km2 section of the Larsen C ice shelf (Figure 1) calves into the Antarctic Ocean. There is concern that the formation of this iceberg will remove a barrier that has been preventing the entire Larsen C ice shelf, with a total area of over 50,000 km2, from sliding into the sea. This is a massive amount of ice. According to this article, if the land-based portion of the Larsen C ice shelf slides into the sea, sea level would rise by 10 cm. Let's try to approximate this calculation. Figure 2 shows a map of the ice thickness. Figure 2: Larsen C Ice Sheet Thickness. It looks like most of the ice sheet is about 650 m to 750 m thick. I will assume the average ice thickness is 700 m, which we can use to estimate the sea level rise as shown in Figure 3. I get 9 cm of sea level rise, which is close enough for a rough estimate like this. Figure 2: Approximate Calculation of the Sea Level Rise Due to the Melting of the Larsen C Ice Shelf. Figure 3 shows a giant rift forming along the edge of the Larsen C ice sheet, which is expected to calve off this year. The iceberg formed is expected to be one of the ten largest ever recorded. Figure 3: Huge Rift Forming Along the Edge of the Larson C Ice Sheet. (Source) Save Save Save Save Save Save Save Save Save Save Save Save Save Save Posted in General Science, News Fact Checking | 2 Comments ## Engineers Say the Darndest Things Quote of the Day After 146 years, the circus could no longer compete with actual reality. Randy Rainbow, comment after the announcement that the Ringling Brothers Circus was going out of business. Figure 1: Art Linkletter, during a segment of his show called "Kids Say the Darndest Things." (Source) I sit in many meetings – hours of meetings every day. Occasionally, people say things that are pretty funny. Some of the comments remind me of those Art Linkletter (Figure 1) used to hear when interviewing children. The children often dropped pearls of both humor and wisdom. When the humor comes from engineers, it is usually pretty dry. In our office, we keep a whiteboard that is filled with some of the choicest quotes from engineers. I thought I would share a few of them with you. In general, engineers do not want to see their names on the whiteboard because some of the statements are pretty lame. Table 1: Interesting Engineer Statements. Statement Context That manager sure is full of leadership. I am sure the engineer wanted to say the manager was full of something else. However, discretion indicated that "leadership" was a better word. I didn't remember that … I was saving my brain for the important stuff. Engineer explaining why he forgot to add some minor detail to a schematic. There is no problem that cannot be solved with more oversight. Engineer who needed some help but instead got additional status meetings with management. The only thing I do right away is procrastinate. Engineer explaining that sometimes it is best to wait for a bit before jumping on a request. Often, the need for the request goes away in a day or two. It is a one-sided PCB … except for the parts on the bottom. PCBs have a top and bottom. The engineer was supposed to design a PCB with parts only on the top – unfortunately, he put parts on the top and bottom. This made the PCB a two-sided board. He is covering his ass in 3D. Engineer's response to a person sending out massive number of ass-covering emails. Before you grease the skids, make sure you know what direction they are pointing. Engineer's response to a manager who used the metaphor "we need to grease the skids" before we actually figured out what we are trying to do. I am the condiment in the buffet of [insert corporate name]. Engineer expressing frustration with their ability to influence decisions. You might have power, but you have no brain. Two engineers discussing a circuit board with a faulty processor. You couldn't make these mistakes without knowing what you are doing. A product returned from the field had been modified by the customer. The modifications were quite sophisticated and included updating a checksum. You can't enjoy anybody's misfortune anymore. This engineer was lamenting the good ol' days when you could derive some satisfaction faction from an opponent's misfortune. I reminded this engineer of the German word "Schadenfreude," which translates roughly as "malicious joy." Save Posted in Humor | 1 Comment ## Temperature Sensing with a Bandgap Reference Quote of the Day Journalism is printing what someone else does not want printed; everything else is public relations. ## Introduction Figure 1: Typical LMT70 Application Circuit. My application circuit will be VERY similar. (Source) I have decided that my next home electronics project will be a precision thermometer that I can read over the Internet. I will be mounting the sensor at my cabin in Northern Minnesota, where winter temperatures can drop to -40 °C or lower. During the summer, temperatures can rise to nearly 40 °C. My plan is to connect the unit to a Raspberry Pie that I use to provide remote monitoring and control. I decided that I going to use a Texas Instruments' LMT70 precision temperature sensor, which uses a well-known circuit called a Brokaw bandgap reference to measure the temperature of its die. This post documents how I familiarized myself with this part and how it works. My Mathcad and LTSpice source files are included here. ## Background ### Definitions Breakout Board Breakout boards are small PCBs on which you can mount an integrated circuit and that provides you readily accessible points for connecting the integrated circuit pads/balls to the outside world using pins and wires. This web page shows a good example of breakboard application. Bandgap Reference A bandgap voltage reference is a temperature-independent voltage reference circuit widely used in integrated circuits. It produces a fixed (constant) voltage regardless of power supply variations, temperature changes and circuit loading from a device. Normally, bandgap reference circuits cancel out two opposing variations caused by temperature. For temperature measurement, we will not be cancelling out the temperature variation – we will use the very predictable variation present in part of the circuit to measure the die temperature of the LMT70. In general, the die temperature is strongly correlated with the ambient temperature. The relationship between ambient temperature and die temperature is usually established empirically. Proportional to Absolute Temperature A circuit parameter (e.g. voltage) that is proportional to the LMT70's absolute die temperature, ie. temperature measured in Kelvin. Note that transistors do not work at absolute zero, but the linear response will extrapolate down to 0 K. ### Requirements This is a home project, which means my requirements will be fairly simple: • The sensor must be able to measure a range of temperature from -40 °C to 40 °C. The LMT70 is capable of measuring from -55 °C to 150 °C, so it has plenty of dynamic range. • I am looking for an accuracy of ±0.5 °C. The LMT70 is rated for ±0.36 °C over the range of -55 °C to 150 °C. I need to ensure that the error introduced by my A/D conversion does not cause my overall error to exceed my requirement. • I need to be able to mount the sensor onto a board that I can build. • The sensor must be capable of being shutdown and only activated periodically. This is my approach for minimizing the error due to self heating. I plan on having the sensor off most of the time and only turning it on for a few seconds every five minutes or so. This approach will minimize the error from self-heating. ### Brokaw Cell The LMT70 uses a Brokaw bandgap reference circuit that can produce an output voltage that is proportional to the absolute temperature. The circuit is well-described on the Wikipedia, so I refer you there for more details. I do want to point out an excellent video presented by A. Paul Brokaw, the developer of the circuit. You rarely see a circuit design presented by its original developer, so this video is a treat. ### Voltage Proportional to Absolute Temperature Equation 1 is describes how the output voltage from a Brokaw bandgap reference varies with absolute temperature. I derive Equation 1 in Figure 2. R1 and R2 are resistors shown in Figure 2.  Eq. 1 $\displaystyle {{V}_{{PTAT}}}=\frac{{{{k}_{b}}\cdot T}}{{{{q}_{e}}}}\cdot 2\cdot \text{ln}\left( n \right)\cdot \frac{{R1}}{{R2}}$ where Except for temperature T, all parameters on the right-hand side of Equation 1 are constants. Thus, Equation 1 describes a linear relationship between VPTAT and T. ## Analysis ### Output Voltage Derivation Figure 2 shows my Brokaw reference circuit built using 2N2222 transistors and a generic high-gain opamp. You can see my Spice commands on the left for setting up the simulation – perform a 1 msec transient analysis at temperatures from 10 °C to 70 °C in steps of 10 °C. I also include my derivation of Equation 1 as it applied to the circuit in Figure 2. In this Brokaw realization, the voltage sum of the VBE across three (n = 3) transistors (Q1, Q2, Q3) plus the voltage drop across R2 is forced to be equal to the VBE drop across Q2. The derivation is easily extended for any number transistors (n > 1). Figure 2: LTSpice Example Circuit and My Derivation of Equation 1. ### LTSpice Simulation Figure 3 shows the results of my LTSpice simulation. I ran the simulation over a temperature range from 10 °C to 70 °C in increments of 10 °C. In Figure 3, the red line corresponds to 10 °C and 70 °C corresponds to 70 °C. I could have ran the simulation over a wider range, but my interest here is expository – the parts have a guaranteed level of accuracy over a temperature range from -55 °C to 150 °C. Figure 3: LTSpice Simulation of Brokaw Cell. ### Simulation Results Versus Theoretical Prediction Figure 4 plots Equation 1 and my LTSpice simulation versus temperature. As you can see, the agreement is excellent. Figure 4: LTSpice Graph of Brokaw Bandgap Reference VPTAT for Various Temperatures. ## Conclusion I go through an analysis like this every time I use a part for the first time. I use a combination of Mathcad and LTSpice to develop simple models for predicting circuit behavior and optimizing my designs. Posted in Electronics | 2 Comments ## Some PT Boat Statistics Quote of the Day It was involuntary. They sank my boat. — John F. Kennedy, on how he became a hero. ## Introduction Figure 1: PT-109 Crew. JFK is on the far right. (Source) I was doing some reading about President John F. Kennedy (JFK) and was surprised to learn that he actually commanded three PT boats: PT-101, PT-109, and PT-59. His service on PT-101 was very short. His next command, PT-109, became famous because of its ramming and sinking by the Japanese destroyer Amagiri. Though injured, JFK was able to lead his surviving crew out of enemy-held territory. JFK also commanded PT-59, with one of its actions dramatized in the movie PT-109. During this action, PT-59 rescued US Marines stranded on a beach while under fire. JFK's service on PT-59 would normally have made it a significant piece of naval history, but an amazing series of bureaucratic screw-ups, including a typing error, caused it to be left to rot at its New York mooring. Figure 2: PT-109 on board SS Joseph Stanton. (Source) This bit of history got me curious about PT boats and their history. I decided to do some web scraping and pull together some statistics on PT boats into this post. As I read the history of PT boats, it became obvious that the US was scrambling during the early years of WW2 to put any type of craft they could into battle. Unlike steel destroyers and battleships, these boats were made of plywood and mahogany. They depended on speed and "hit and run" tactics to survive. The actual web scraping (source) was a bit complex: copied the page into Notepad++ and used a regex to clean things up, Power Query to do the parsing and transformations, and tables were generated using Excel. My source files are here. ## Background ### What is a PT Boat? The popular term PT boat came from their US Navy designation of Patrol, Torpedo. They were fast attack craft that were armed with torpedoes, depth charges, and machine guns. Their use was almost exclusively limited to WW2 – four boats were built during the Korean War. The PT boats were small, fast, and inexpensive to build because of their wooden construction. Their effectiveness was hampered by ineffective torpedoes (fixed later in the war), limited armament, and lack of armor. ### What was their Role? Figure 3: PT-32, one of the PT boats that evacuated MacArthur from the Philippines. (Source) The PT boats were small and generally limited to coastal operations. In combat, they were most known for their commerce raiding during the Solomon Islands campaign , and were particularly effective at attacking Japanese barge traffic, which was critical to starving remote Japanese garrisons. Early in WW2, four PT boats performed an important special operation by evacuating MacArthur and his staff and family from the Philippines (Figure 3). ## Analysis ### Who Built Them? The US built 813 PT boats during WW2 and Korea (Figure 4). There were only 4 built during the Korean conflict (1951), so 809 were built just prior to and during WW2. Notice that a British company built a US PT boat, which served as one of three prototype boats used by the US Navy to develop the entire class. Figure 4: PT Boat Manufacturers. As you can see in Figure 4, the Elco and Higgins companies dominated the production of PT boats. ### Differences in PT Boat Construction The bulk of the PT boats built were 78 or 80 feet long (Figure 5). Many of the smaller boats were eventually removed from combat roles and used as utility vessels (also known as small boats). The four largest boats (89 ft, 94 ft, 98 ft, 105 ft) were aluminum boats built during the Korean conflict. Figure 5: Length of PT Boats. See this article for an excellent discussion of the various PT boat classes. I should mention that US was the world leader in the manufacturing of marine plywood during WW2. The key was the development of waterproof adhesive in 1934. This technology proved critical when the US needed to build large number of inexpensive small watercraft, like PT boats and landing craft. ### Who Received Them? Excluding three prototypes, 527 PT boats were built for the US Navy and 283 were built for our allies (Figure 6). Figure 6: PT Boat Allocations By Nation. ### What Happened to the US PT Boats? Figure 7: PT boat burning in the Philippines. (Source) Figure 7 shows that most of the US Navy PT boats survived the war and were sold, scrapped, abandoned, or destroyed at the war's conclusion. Many of the destroyed boats met their end by being dragged onto beaches and burned (Source, Source). The non-combat losses were due to weather or grounding, which was a problem for boats running in uncharted shallow waters. Thirty-four boats were lost in combat, with one boat was lost due to ramming, JFK's PT-109. Figure 7: Fate of the PT Boats. ## Conclusion I am always amazed when I read about how both wooden boats and aircraft (ex. deHavilland Mosquito) played significant roles in WW2. Unfortunately, ships built of wood require careful maintenance and few PT boats survived into modern times. Here are a few links to the existing PT boats that I know of: Figure 8 shows a PT boat running similar to PT 109 running at high speed. Figure 8: PT-105, a PT boat very similar to PT-109. (Source) ## Fact Checking Power Over Ethernet Marketing Math Quote of the Day In the case of good books, the point is not how many of them you can get through, but rather how many can get through to you. Mortimer J. Adler. He wrote a book called How to Read a Book that helped me become an effective reader. ## Introduction Figure 1: Example of very neat network cable bundles. In PoE applications, these cable bundles can experience significant self-heating, which will reduce their load capacity. (Source) I was reading a blog by a cable manufacturer (Belden) this morning on the advantages of using Cat 6 cable over Cat 5e for network installations going forward (Figure 1 shows a great example of network cabling). Normally, I see the cable manufacturers recommending Cat 6 to customers because it will allow them to upgrade to 10 Gbps Ethernet, at least for runs less than 55 meters long. The blog I read this morning took a bit different approach. It was encouraging customers to switch to Cat 6 because it consumes less power in Power over Ethernet (PoE) applications. It made the following testable claims: • As much as 20% of the power through the cable can get “lost” in a 24-gauge Category 5e cable [relative to a Category 6 cable], leading to inefficiency. • As we mentioned above, losing nearly one-fifth of the total power in a 24-gauge Category 5e cable may seem like a lot of power loss – and it is. But doing the math will show you that the total dollar amount comes out to be only around$7 per year.

I will check these claims in this blog post.

## Background

Table 1 summarizes some of the key characteristics of Cat5e and Cat6 cable. For this blog post, the important difference from a PoE standpoint is the wire gauge.

Table 1: Key Characteristics of Cat5e and Cat6 Cable.
Characteristic Cat5e Cat6
Max Bit Rate (bps) 1,000 10,000
Approximate Cost ($/foot) 0.3 0.5 Frequency Bandwidth (Mhz) 100 250 1000 BaseT Reach (m) 100 100 10000BaseT Reach (m) – 55 Wire Gauge (AWG) 24 23 ## Analysis ### Claim 1: 20% Power Loss on a PoE Line. I was not able to confirm the 20% loss of the total power loss being attributable to the wire resistance. It is easy to confirm that as much as 15% of the total power loss is attributable to wire resistance. Figure 2 shows my calculations. So I would say that there claim is close to true. Figure 2: I calculate 15% for the maximum loss percentage. ### Claim 2:$7 Per Year Per PoE Line Cost.

I was able to confirm their claim that each PoE line burns \$7 per year in the wire resistance (Figure 3). That was a bit surprising.

Figure 3: Annual Electrical Cost for a Running a PoE Line in the US.

## Conclusion

I read marketing claims all the time. Most of the time, there is some level of reality to them. In this case, one claim is close and the other is accurate. I was surprised at the cost yearly cost incurred because of the cable resistance.

Save