United States 2020 Presidential Election Simulator v2 🔵 🔴
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!
