United States 2020 Presidential Election Simulator v2 🔵 🔴

Introducing the second instalment

Andrew Moss
Oct 12, 2020 · 5 min read

This article discusses v2. For v1, go here.

Recently I posted an article about a US 2020 election simulator I had created, and I’m pleased with all the positive feedback I’ve received. I have made a few tweaks and added additions for version 2, which I will go through.

⚠️ Microsoft 365/Excel 2019 for Windows/macOS Required

Download the file: 2020-US-Election-Simulator-v2.xlsm

Discussion about this workbook can also be found at Reddit.

Additions

Automated State Strength

In v1, the strength of each state’s political leaning according to the poll averages were taken from the Financial Times’s election tracker. You could manually update the Strength column by choosing from the following categories: SOLID DEM, LEAN DEM, TOSS-UP, LEAN REP and SOLID REP. The big problem was that with some states jumping between categories daily, it annoyingly meant the column regularly become out of date, so you had to do the leg work.

The big change made is it is all automated now. Whereas the FT’s tracker used predictions found at RealClearPolitics, in v2 the predictions of 14 different sources on a Wikipedia article are pulled into the workbook dynamically.

There was a process to go through in order to make this data usable for the table in the 2020 US Election worksheet. The different categories needed to be converted into a uniform graded system. All the sources used the same kind of system, but with subtle differences. Some classified states as ‘solid’ instead of ‘safe’, and in the case of Inside Elections (IE), there was a ‘tilt’ category. The Weighting column ensured that each classification was given an appropriate amount of points based on its worth. Adjacent to this, the Type column merges the party identifier (or ‘T’ for tossup) with the number of points according to the value of the category.

Chance Conversion is a replica of the main table, except all the state strength categories have been converted into codes.

These are counted up in the Breakdown table.

The counts are then multiplied by their weighted value for each party, before being totalled.

Each party’s total for a state is subtracted by the corresponding figure in its rival table. This produces a positive number in one and a negative in the other — or zero for both is there is no difference.

These ratings are then scaled from 1 to 8, with Safe D and Safe R being 1.00 and 8.00 respectively.

The Categories table lists the final classifications alongside the threshold values that determine the boundaries of where each category starts and ends.

Using the values from the R column in the Rating (1–8) table, the category that pertains to each one is returned.

Now go back to the 2020 US Election worksheet and click on the State Strength button under the Default tab in Scenario Manager, and the Strength and 2020 columns will populate.

⚠️ Potential Problem

Although the query works fine the majority of time, you may find there to be issues loading it if there has been a sudden change on the Wikipedia article. This is the one downside to pulling in data this way. You are very much dependent on everything staying put.

It is important to remember how the query works. Excel connects to the specified website and then the desired item is selected in Navigator. For what we are looking for, in most cases it is Table 16, but recently I ran into problems because a loading error message popped up. I had to reconfigure the table, as I found it had changed to Table 11. However, a few days later it reverted back.

If this does happen, go to From Web in the Data tab.

Enter the link https://en.wikipedia.org/wiki/2020_United_States_presidential_election and click OK.

Check to see which table houses the state prediction data, but click Cancel afterwards.

Ensure the current active cell is pointing somewhere in the table within the Media Predictions worksheet. The Query tab will be present in the ribbon; click on the Edit button.

In the APPLIED STEPS pane on the right, press the gear icon in the Navigation step.

Choose the appropriate resource that pertains to the state predictions table and click OK.

You’ll probably find that a new step has been automatically generated called Changed Type1. Delete this.

Click Close & Load to confirm these changes. The query should now load correctly.

Intersection highlighting

Row and column headings of the active cell are highlighted to serve as a visual aid enhancement so you can see more easily which state you are changing.

Result Counter

To make it easier to see how many results have been entered, I’ve added a result counter.

Improved VBA

I replaced all the cell references I used in the VBA code with named ranges. These can be found in the Name Manager within the Formula tab.

Final Words

If you have any feedback to give about the workbook or suggestions you think I should implement, please do tell me on here or in the Reddit thread I set up. Thank you!

The Startup

Get smarter at building your thing. Join The Startup’s +792K followers.

Sign up for Top 10 Stories

By The Startup

Get smarter at building your thing. Subscribe to receive The Startup's top 10 most read stories — delivered straight into your inbox, once a week. Take a look.

By signing up, you will create a Medium account if you don’t already have one. Review our Privacy Policy for more information about our privacy practices.

Check your inbox
Medium sent you an email at to complete your subscription.

Andrew Moss

Written by

I mainly focus on Excel-related content in my articles, often providing example workbooks to download, too. https://www.linkedin.com/in/andrewcharlesmoss

The Startup

Get smarter at building your thing. Follow to join The Startup’s +8 million monthly readers & +792K followers.

Andrew Moss

Written by

I mainly focus on Excel-related content in my articles, often providing example workbooks to download, too. https://www.linkedin.com/in/andrewcharlesmoss

The Startup

Get smarter at building your thing. Follow to join The Startup’s +8 million monthly readers & +792K followers.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app