Friday, November 18, 2011

Tableizer ... from tab chart to html table

A not-so-quick post about converting a tab chart in Excel into an HTML table in a jiffy (toward the end), and more detailed info about how and why I made the table to begin with. ...

Yesterday I wrote a blog post comparing the success of Big 12 NCAA football teams against the betting line. I was interested in the data because this weekend the University of Texas (my alma mater, FWIW) is favored by 8 points over Kansas State, which seems absurd. Texas -- a team that has really only seen success when running the ball this year -- will probably be down to their fourth-string running back. Their quarterback hasn't done that well when taking to the air and will most likely be missing his best receiver. Kansas State, on the other hand, is clearly a running team that knows how to pound the ball and find a win.

I guess Vegas has to set the line to counter a bunch of Texas homers who bet on UT even though they will most likely lose? I dunno ... I don't follow betting much, but I did find a comment on talk radio interesting that Kansas State "doesn't get respect" in Vegas. I decided to see how they fare against the line, along with other Big 12 teams.

I found the data on covers.com and put it into a spreadsheet, using text-to-columns to clean up the data, including separating the Line column to have the W and L away from the point spread, which I didn't think I wanted to show.

At first I thought I would do a fever chart with teams in different color lines but couldn't figure out a good way to do it, so I put in a call to Matt Stiles to talk out what might be the best way to visualize this. He, of course, went immediately to the best answer of a simple chart with the teams as columns, the weeks as rows and the data points as the wins & losses.

Once I saw that in my head (and in an Excel example) I knew I could pull that off myself with a pivot table. Maybe I did it the hard way, but I knew I needed values for the wins and losses vs. the line, so I created new rows and used  the IF string command to convert them to numbers. I use the formula =IF(G2="W",1,0) which said if the cell is a W then make write the number 1, if not, write 0. This means only wins would be counted when I created my pivot table.

I discovered during the writing of this post that the IF statement wasn't a good one. There was one game that didn't have a line, which had "N" as a value. That ended up being counted as a loss with a zero. A better formula is =IF(G3="W",1,(IF(G3="N","N",0))) which says if the value is W, make it a 1. If not, check if the value is N and make it N, if not, make it 0 (which is all the L's)

I did the table with my columns as the teams, the rows as the weeks and my data points as the new 1/0 column. This allowed me to add up the wins vs. the line per team, then I manually ranked them from left to right.

To make it pretty and readable, I copied the values of my pivot table into a new sheet, then searched/replaced the 1 for Ws, the 0s for Ls and the blanks for Ns. I also color coded it in Excel, but really had to do that again later. It's about here I'm thinking, how am I going to show this online? Take a screenshot of my pretty Excel spreadsheet? That could work, but probably not the best way.

So, I pulled out an tool by Danny Sanchez called Tableizer, which allows you to copy and paste the tab-delimeted text from an Excel file and then poof! A table! This is something I used to do easily using BBEdit on a Mac, but alas the Macs have been phased out of our newsroom ;-(. Too bad Notepad++ doesn't have that handy-dandy function.

I did have to do some modification of the table after it was tableized ... searched and replaced the W with W so I could use a style to color the background of that cell. I did the same with the L and N cells.

So, enough talking about it ... here is the table:


Big 12 Teams vs betting line this season

WeekK-StateOK StateMissouOUKansasTexasTexas TechIowa StateBaylorTexas A&MTotal Big 12 Wins
Week 1LLLWWWWLWW6
Week 2NWWNWLNWNN4
Week 3WWWWLWWWNL7
Week 4WWWLNNLNWL4
Week 5WNNWLWWLLL4
Week 6WWLWLLWLWL5
Week 7WWWLWLLLLW5
Week 8WWLLLNWWNL4
Week 9LWWWLWLWLL5
Week 10WLLWWWLLWL5
Week 11WWWNWLLNLL4
Team Wins vs. Line8866555442

Betting line history via covers.com | Created with help of Tableizer | Hat tip to Matt Stiles for ideas on the visualization.

"W" is a win vs. the line, "L" is a loss vs. the line and "N" means there was no game or no line for that game.

0 comments:

  © Blogger template 'A Click Apart' by Ourblogtemplates.com 2008

Back to TOP