Random Data Generator Files

Owned by Jonathan Schmale
Oct 22, 2020
3 min read

The data for the Election Rehearsal Tool can be generated using an Excel sheet with formulas and macros. A sample of this is the attached file which has been customized for SK data.

How it works

The original sample data worksheet is driven by formulas based on random numbers and some customizable values. Each party needs a column with it’s abbreviation as its title. Once the party columns are added and the custom control values set then a macro is run. The macro can be run with the CTRL-Shift-C keys.

  • creates a new sheet for each riding number between the min and max riding numbers in fields A2, A3

  • copies the data on the “Sample” data sheet

  • Recalculates all the formulas triggering new random data to be generated

  • Converts all the data to values to eliminate the formulas

  • Removes the header rows used as input to calculate the data

  • Removes the sample sheet itself

  • prompts you to do a Save As … to save the new data as a new file

    • I tried to actually open the Save As dialogue but it wasn’t working so a simple message box is all

Customizing

The customizable values include the

  • number of ridings to generate (A2-A3)

  • min and max polls in each riding (B2-B3)

  • polls per step to add to report (C2-C3)

  • step to start reporting at (C4)

  • min and max number of votes assigned to each party in each step (D2-D3, E2-E3, F2-F3 … )

  • the weighted probability of a party being the winning party (D4-D5, E4-E5, …)

Notes

The generator can handle the situation where there are 0 new polls reporting in a step. In that case the generator applies the same number of party votes as existed in the previous row.

If the number of polls reaches the total polls before the final step the generator stops increasing party votes.

Winning Party

The winning party is primarily determined by which party is randomly selected based on a weighted probability of sorts. The min/max votes per party is based on formula that gives extra votes to the party selected to win. It is possible that more than one party will randomly be chosen to win in which case there will be a close race between them.

Polls Per Step

I have introduced a formula that changes the min/max of polls per step based on a threshold of the number of polls in the riding. For smaller number of polls you want the min/max to be smaller.

Federal Election Idea

If you wanted to have different customized party probabilities for each range of ridings across the country you could:

  • Remove the step of the macro that deletes the “Sample” spreadsheet

  • Customize the party values for the first region of ridings

  • Generate with the starting set of ridings (say 1 - 15)

  • Update party values

  • Generate with the next set of ridings (say 16 - 42)

  • and so on