Grim New Spreadsheet: From tracking enrollments to tracking the sick & dying.
Nearly seven years ago I made my mark in the world by becoming known as the Obamacare Enrollment Guy here at ACASignups.net.
I’m still posting updates, of course, but lately, about 95% of what I’m writing about and analyzing, like everyone else in the country, is related to the ongoing COVID-19 pandemic.
As I noted back on March 11th, this time around I’m not the one doing the hard work of actually gathering the data itself. Several other outlets/organizations have taken up the grim task of keeping a running tally of how many people have tested positive for coronavirus, how many are hospitalized, how many have died and how many have recovered.
Two of the most popular and reputable sources I've found so far are WorldoMeter and The COVID Tracking Project. Worldometer seems to have more up-to-date data on confirmed cases and deaths from the virus, while the COVID Tracking Project also keeps track of how many people have actually been tested total (including negatives). They also keep tabs on pending test results and hospitalizations.
What I have done is to create an ambitious spreadsheet which puts the corresponding data from both sites together, with a few additional twists:
- First, I've included the estimated total population of every U.S state/territory via Wikipedia (as of July 2019)...
- ...which allows me to add new columns listing both the confirmed COVID-19 cases and deaths per capita. This gives a much clearer picture of how relatively ugly things have gotten in each state/territory to date.
- Second, I've added columns to rank the daily percent increase in both confirmed cases and deaths for each state/territory (on the right side), which is important for tracking the rate of the virus' spread.
- Third, I've added the Presidential partisan lean of each state as well as which party holds the governor's seat. This may seem incredibly inappropriate (and it is), but it's sadly necessary because Donald Trump has apparently decided to only grant his favor and any substantial assistance to states which a) voted for him and b) whose governors kiss his ass enough.
I'm quite serious. On March 27th at 10am, I cynically Tweeted out the following:
Problem with this is that Trump will FLOOD red states with money, equipment and resources after starving the blue states. #MAGA will praise him for it. Then again, he’s failed to do so for swing state Michigan so far because we have a Dem Governor, so... https://t.co/ULELuUcxqc
— Charles Gaba (@charles_gaba) March 27, 2020
Regular as clockwork, on March 28th at 7:09pm...
MA received 17% of requested medical supplies from the Trump admin. ME: 5%. CO: 1 day's worth.
FL got everything they requested. + an identical shipment the next week. oh, and a 3rd is on the way.
— Jesse Lehrich (@JesseLehrich) March 29, 2020
Anecdotally, there are wide differences, and they do not appear to follow discernible political or geographic lines. Democratic-leaning Massachusetts, which has had a serious outbreak in Boston, has received 17 percent of the protective gear it requested, according to state leaders. Maine requested a half-million N95 specialized protective masks and received 25,558 — about 5 percent of what it sought. The shipment delivered to Colorado — 49,000 N95 masks, 115,000 surgical masks and other supplies — would be “enough for only one full day of statewide operations,” Rep. Scott R. Tipton (R-Colo.) told the White House in a letter several days ago.
...Florida has been an exception in its dealings with the stockpile: The state submitted a request on March 11 for 430,000 surgical masks, 180,000 N95 respirators, 82,000 face shields and 238,000 gloves, among other supplies — and received a shipment with everything three days later, according to figures from the state’s Division of Emergency Management. It received an identical shipment on March 23, according to the division, and is awaiting a third.
“The governor has spoken to the president daily, and the entire congressional delegation has been working as one for the betterment of the state of Florida,” said Jared Moskowitz, the emergency management division’s director. “We are leaving no stone unturned.”
President Trump repeatedly has warned states not to complain about how much they are receiving, including Friday during a White House briefing, where he advised Vice President Pence not to call governors who are critical of the administration’s response. “I want them to be appreciative,” he said.
Sure enough, as you can see, so far the first 15 states or territories on a per capita confirmed case basis are either solidly Democratic, have a Democratic Governor (Louisiana, Michigan, Pennsylvania) or are considered “irrelevant” by Trump from an Electoral College POV (Guam, US Virgin Islands). As of today, the first fully Republican-controlled state is Mississippi.
Even from a cold-hearted political POV you might think Trump would be sucking up to Michigan and Pennsylvania since they’re swing states...but his pettiness and ego won’t let him do so: They both have Democratic governors who are refusing to kiss his ass, therefore they must be "punished" in his view.
Swing states with Republican governors, on the other hand, are a different matter: He’s already giving Florida more than they asked for, and my guess is that he’ll be generous with Mississippi, Georgia, Indiana (Pence’s home state!) and Tennessee in the coming weeks as well if they start moving up the per capita case chart.
By default the spreadsheet is sorted by per capita confirmed cases (the yellow column header in the middle). To sort it differently:
- Select columns B3 - Q59
- Click the "Filter Views" icon at the top left (it's next to the printer icon)
- Click "Create New Temporary Filter" to make the columns sortable
- Choose which column you want to sort the data by
I plan on updating the spreadsheet with a new worksheet every evening, which will let me pull the data from the previous day into the daily % increase columns
I pray this will be a short-lived project, because if it's necessary for as long as ACASignups.net itself has, we're in serious trouble. :(