The Excel Web Query and Predicting the Washington State Governor's Race
Web Queries in Excel let you grab tabular data off of a web page without writing any code or messing with web services. I use this feature all the time as a manager trying to grab internal business data off of many different systems in order to analyze it. In this example, I build a computer model of the political race. The entire project can be done in less than 20 minutes.
- Open Excel 2003
- Choose Data | Import External Data | New Web Query...
- Browse to http://vote.wa.gov/general/status.aspx and select the table that lists ballots left to be counted by county.
- After the table is imported do it again for the results in the race found so far found at http://vote.wa.gov/general/resultsbycounty.aspx?o=3001&t=s
- You should now have both tables side-by-side in Excel. You can color and format them however you like:
- Create further columns to find the percentage a candidate gets from each county and then multiply that percentage against the remaining ballots in that county.
- Finally, total the projected votes with the votes a candidate already has and you will arrive at a projected final outcome.
- Now here is the really cool part: whenever new totals are posted on the web sites you can update your spreadsheet with Data | Refresh Data. Excel keeps your nice formatting and fomulas and just gives you the new numbers.
A final solution can be downloaded here.
