Voting Excel Template and Add-In (Borda Counting and Schulze Method)
March 7, 2011 5 Comments
It is often necessary to find group opinion on a subject when making technical (or other) decision. Whether looking for group consensus, either a single option or prioritizing a set of conflicting candidates either human or technical in nature. I’ve had to sit through many of these electoral processes over the years, and can understand, having seen first-hand, just how lacking in rigor they often are. In order to get true buy-in, any ballot or ranking system must be seen to be fair, even by those who lose.
In an effort to improve scoring and voting systems, I’ve created a template spreadsheet, and Excel Add-In that allows two common election methods to be carried out in a consistent way.
Download the template and add-in from here
The two voting systems I’ve chosen are the Borda Counting system, and the Schulze Method, the latter becoming the preferred ballot system for many public elections (Wikipedia, amongst a long list of others). First of all, let me briefly describe the two voting algorithms.
Borda Counting
Borda Counting is a simple voting algorithm that is often used in the workplace to rank a variety of options, candidates or any other electoral system. It simply asks voters to order the list of candidates (candidates may take any form, people, products, or animals). Each ballot is tallied by giving the least preferred candidate a zero (0) score, and each subsequently preferred candidate in order, plus one (+1) from the previous candidate until all candidates are scored. For example if four candidates, A, B, C and D were voted on in a ballot A>B>C>D, then A would receive a score of three (3), B would get two (2), C get one (1) and D get zero (0). Every ballot would be tallied and summed to get a total score for each candidate. The winner is the candidate with the highest score.
Borda Counting’s strength is its simplicity, and its ability to bring to the forefront a winner that may not be the one voted most preferred the most often. This sounds weird, but if more people vote for a candidate in the higher positions, and vote the simple first-past-the-post winner last, then the other candidate has been voted preferred in the election as a whole. This makes ballots appear to represent the voters intention more clearly and properly considers voter preferences rather than just the most preferred candidate.
For example –
5 voters vote BACD, 1 voter ACDB, 2 voters CADB, and 1 voter DACB.
The Borda Counting results are: A>B>C>D. Surprised? 5 people voted for B being first. But this wasn’t enough to push out A which was voted for second place 4 times. B was voted in last place too often, and A was voted in first or second place in all cases. This is referred to as a Condorcet Paradox – big words meaning -“even though more people voted for a candidate in a pair-wise run-off, a candidate still loses!”
Before writing off Borda Counting, it is one of the better voting algorithms, and is easy for all voters to understand the math and logic; this isn’t always the case with other methods, lets consider the Schulze Method.
The Schulze Method
The Schulze Method takes an approach that the winner of an election should be the candidate that not only the most people want to win, but also the least people want that candidate to lose. It builds a pair-win (or pair-defeat) matrix of the number of time one candidate is preferred over another in all ballots. Unlike the Borda Counting algorithm, Schulze Method looks solely at a “win” when one candidate is preferred over another. Once this matrix is built, the relative strength between all candidates and paths to other candidates is built looking for the weakest value between all candidates and all paths, either directly or indirectly. For example, if A beats B, and B beats C, then A would indirectly beat candidate C. The winner is the candidate who has the strongest path win over all other candidates. The permutations grow as the candidate count grows, and for full details on the algorithm, the inventors article “A New Monotonic, Clone-Independent, Reversal Symmetric, and Condorcet-Consistent Single-Winner Election Method” and Wikipedia article offer pages of demonstration. Schulze Method’s weakness is its complexity; explaining it to a group of people, especially in a single sentence is hard – but it has undergone many years of mathematical scrutiny and offers one of the most tamper-proof (susceptible to tactical voting) options available.
The Schulze Method satisfies the criteria for not being susceptible to the Condorcet Paradox, and given our prior example in Borda Counting for example –
5 voters vote BACD, 1 voter ACDB, 2 voters CADB, and 1 voter DACB.
The result using the Schulze Method is B>A>C>D. I’ll leave it up to you to decide if that represents more voters choice that the Borda Counting result (which made A the winner, even though more people voted B as their most preferred). Before you choose Schulze Method universally, a Condorcet Paradox is easy to build when dealing with small numbers of voters, but rarely eventuated when the number of ballots grows substantially. I support both, and investigate the results if they don’t agree – which in practicality is rarer than this example might indicate.
Voting Systems Excel Add-In
Download the template and add-in from here
I wrote this add-in to make it easier to run fair ballots, and to experiment with the different voting systems. The features of this template and add-in are –
- Supports Borda Counting where a total score for each candidate is arrived at by their position in a listed set of most-preferred to least preferred candidates
- Supports Schulze Method of voting where the most preferred candidate that wins a pair-wise election over every other candidate is the winner. This voting system is becoming a favored choice for many public elections, due to its resilience in the face of tactical voting which many other systems (like Borda Counting) can suffer.
- Support Tied candidates where the voter wishes to express no opinion of one candidate being better or worse than another.
- Support skipping candidates, where the voter expresses skipped candidates are less preferred than all candidates voted for, but no better or worse than each other.
- Provide a printable ballot template that explains the rules clearly to voters.
- Provide a set of Excel functions that help interpret and analyze the results.
The spreadsheet template allows ballots to be tallied in shorthand syntax (B more preferred than A, then D then C for the first votes). The most simple ballot case might be 5 ballots, split this way –
Count | Preference Sequence |
3 | BADC |
2 | BCAD |
This would give results using both of the supported systems of –
Borda Counting | |||
Result: | B>A>C>D | ||
Score | Rank | ||
A | 8 | 2 | |
B | 15 | 1 | |
C | 4 | 3 | |
D | 3 | 4 | |
Schulze Voting | |||
Result: | B>A>D>C | ||
Rank | |||
A | 2 | ||
B | 1 | ||
C | 4 | ||
D | 3 |
Installing the Add-In
This add-in makes Excel User Defined Functions (UDF’s) available to the open excel spreadsheet. To use this add-in,
- Download the “Voting – GeekSpeakDecoded Ballot Template 1_0.zip” file and unzip it to a directory of your choice. There are numerous files in this zip file, and they all must be in the same folder. Download the template and add-in from here
- Open the spreadsheet “Voting – GeekSpeakDecoded Ballot Template.xlsx” file
- Open the add-in “VotingSystems.xll” file (seems strange doesn’t it, but it won’t close the current spreadsheet, just choose File-Open and navigate to the VotingSystems.xll file)
- Add-ins are a particular security risk for Excel, if you trust the add-in, enable Macro’s for the session by clicking “Enable Add-In for this session only”
What Is Included in the Spreadsheet Template?
The spreadsheet template has four main pages. These worksheets are –
Examples – Shows each of the functions added by this add-in in operation. It also shows how to get the resulting ranking using both of the supported voting systems, and how to analyze in more detail the results.
Your Ballot – A blank worksheet ready to enter the results for a ballot of your own.
Printable Ballot Sheets – A template for creating your own paper ballots. This template contains the rules for proper voting using this spreadsheet and the supported skipping and tied value features.
Reference – More detail on the algorithms used, and detail on each function available in the VotingSystems.xll add-in.
The examples and reference worksheets form the bulk of the documentation required, and by experimenting with the Your Ballot worksheet, it is easy to get up and running.
For more advanced worksheets, the custom functions added can be used for building your own template in a format of your choosing. The custom Excel functions added when this add-in is installed are:
BordaRankings | Given a list of ballots, returns the resulting preference order using Borda Counting. | |
SchulzeRankings | Given a list of ballots, returns the resulting preference order using Schulze Method for voting. | |
IsBallotValid | Returns True (for a valid vote) or False (given an invalid vote) given a ballot vote string. | |
InvalidBallotReason | Returns the reason a ballot vote string is invalid. | |
BordaCandidateScore | Returns the individual candidate score for a set of ballots in a Borda Counting election. | |
BordaCandidateRank | Returns the individual candidate rankingfor a set of ballots in a Borda Counting election. | |
SchulzeCandidateRank | Returns the individual candidate rankingfor a set of ballots in a Schulze election. | |
PairWinScore | Returns the number of wins from one candidate over another in a Schulze election (or Borda Counting election, but pair wins play no part in the Borda Count result). | |
PathStrengthScore | Returns the path strength from one candidate over another in a Schulze election. See http://en.wikipedia.org/wiki/Schulze_method | |
RankOrderCentroidValueBorda | Retuns the ROC for a given candidates final ranking in a Borda Counting election. ROC’s are used to distribute weights that sum to 1 when for decision support (e.g. when choosing a software package) | |
RankOrderCentroidValueSchulze | Retuns the ROC for a given candidates final ranking in a Schulze election. ROC’s are used to distribute weights that sum to 1 when for decision support (e.g. when choosing a software package) |
Summary
I’ll be doing future work on this add-in and future articles will cover the algorithms and the pro’s and con’s of each voting systems (when to choose one over another), and this spreadsheet will be the tool used to examine the voting results. Please comment on how to improve the experience of this template and share your thoughts on how you used it in your business and technical decisions.
Just note that the Schulze method doesn’t by definition produce an ordered list. I’m guessing you’re removing the winning candidate and running the method again until you run out of candidates? If so, the result it produces isn’t proportional. This is fine if you want a list of winners to replace the old winner in the event the first isn’t able to serve.
If you intended on producing a proportional list with similar proporties to the Schulze method, take a look at Schulze PR (as defined in section 6 of http://home.versanet.de/~chris1-schulze/schulze2.pdf). I have an open source implementation available at http://vote.cognitivesandbox.com if you’re looking to implement this type of ordering. Just note that you’re going to have a really difficult time getting it working in Excel.😉
Good point Brad. Yes, the winner is the candidate that beats all others, and the second is the candidate that beats all of the remaining and so on.
I’ve had a quick read of the Schulze PR, and STV – I can see why you are proud of your implementation! This is all written in C#, but even so, i’ve got to read the C example code some more (http://m-schulze.webhop.net/schulze3.zip).
For those interested, see http://en.wikipedia.org/wiki/Schulze_STV for more information.
Also note how your iterated Schulze method, Schulze STV and Schulze PR differ (details here).
I’ve been meaning to clean up Wikipedia’s Schulze STV article (and create a Schulze PR one). The walk through the algorithm provided there is a little obtuse. I have a sketch illustration that helps get the idea across. Just a touch on the lazy side lately.
Thank you for publishing this. Borda counts are helpful beyond election systems in the narrow sense. Preference structures are often expressed in ordinal variables, with numerous ties. In one of my work areas, disaster needs assessment methodologies, communities each rate their needs by sector on an importance scale. Sometimes we force them to designate only a small number of sectors, say e.g. water (3 points), food (2 points), shelter (1 point), all others scoring 0. The Borda count for sectors over all assessed communities is trivial in a spreadsheet. The Schulze method might be more valuable, but the letter sequences that your add-in requires are difficult to calculate efficiently. I was wondering whether you might want to write a macro, for a table with sectors as columns, and assessed units as rows, that would produce a table that can be copied into your Ballot Entry table in sheet “Your Ballot”. Thanks for your response in advance. Aldo
Hey hey. Don’t know if I passed this on, but I implemented your non-proportional Schulze iteration into python-vote-core https://github.com/bradbeattie/python-vote-core/blob/master/pyvotecore/schulze_npr.py and made the option available on https://modernballots.com. Seemed reasonable enough.🙂