Reader's Enhancement to 2008-2009 College Football Bowl Spreadsheet

This is what I love most about the Internet: collaboration with complete strangers.

A couple days ago I got an email from one of my readers, Jonathan Sickinger. Jonathan said that he searched on Google for bowl spreadsheets and found the one I did. (BTW, I saw that I'm the number one hit for a great variety of college bowl pool spreadsheet-related queries...wow!) Jonathan said that after playing around with the spreadsheet for a few days he decided to make some edits and customizations. Great! He made some cosmetic changes to match an entry form that he sends out to his pool participants. I like the look better than what I had done, personally. Next he added space for up to 40 participants (over my 35).

Then Jonathan said that he had an epiphany: In addition to the individual tracking charts, wouldn't it be great if we also had a chart for each bowl? These charts allow participants to see the pick dispersion for each game, giving them a sense of how important that game is to their overall chances. For example, if everyone in the pool weights the same game as one of their most confident picks then the relative importance of that game drops significantly. Follow? Jonathan also threw in some data on each game to show where the bowl ranks in average points wagered and the distribution of who picked which team. Very cool.

I thought it'd be nice to share with everyone this new and improved version and it's accompanying entry form, so here you go:

0809_College_Bowl_Pool_worksheet_Sickinger_mod.xls
0809_College_Bowl_Pool_Sickinger_entry_form.xls

Note: the file's a bit heftier now at >2,500 KB vs. the roughly 1,200 KB it was before, but I think the new features are worth the added weight.

Next up? Jonathan and I are thinking about collaborating on a VBA-based enhancement to make it so that once a pool organizer gets everyone's entry forms back, he or she can simply click a button to pull everyone's picks into the master workbook. That'll be sweet, no?

Later,

Excel_Geek

Pulling Images into Excel from Websites

You may recall a post I did just over a year ago about pulling images into Excel. That project involved pulling images from directories on your computer. I just completed another $50 Project where the client wanted to pull images into Excel from a website. Really the process was the same, except that this client is using Excel 2007, so I had to head Jonah's advice (see comment on that last project's post) and instead of directly pull the images using

ActiveSheet.Pictures.Insert().Select

I had to first draw an appropriately sized rectangle, move it to the appropriate cell, and "pull the image" by making it the background fill for that rectangle. Maybe someday Microsoft will fix this little idiosyncrasy in Excel 2007, but we'll not hold our breaths.

In the end it's a nice little file. Insiders, your file's on it's way.

Later,

Excel_Geek

The Lawyer's Guide to Microsoft Excel 2007

I don't know why, but when a lawyer friend of mine sent me this, it struck me as terribly funny.

The Lawyer's Guide to Microsoft® Excel 2007

"Spreadsheet programs are one of the most powerful but underutilized tools in the trial lawyer's toolbox. John Tredennick's concise, clear book shows you how to use spreadsheets powerfully and quickly. I believe that every litigator should be familiar with the contents of this book." -- Joe Kashi, Esq., Author

Maybe it's just this Excel_Geek's frame of mind, but isn't a spreadsheet "...one of the most powerful but underutilized tools..." in almost any profession's toolbox?

No offense to John Tredennick. I'm a fan of anything and everything Excel, but what's next? One of these specialized titles?

The Rancher's Guide to Microsoft® Excel 2007
The Cab Driver's Guide to Microsoft® Excel 2007
The Hitchhiker's Guide to Microsoft® Excel 2007

Too funny.

2008-2009 College Football Bowl Spreadsheet

Remember that college football bowl pool spreadsheet I did last year (and the year before )?

Well...I guess it's now a holiday season tradition. Here's the same one updated for this season's match-ups. My gift to you.

0809_College_Bowl_Pool_worksheet.xls

This year's has been updated so that you can track up to 35 participants, and I've reformatted the individual charts sheet, so that it prints out nicely.

Merry Christmas,

Excel_Geek

Pulling Company Financial Statements from Yahoo Finance

Here's an interesting $50 Project I just completed. The request was for a spreadsheet that would allow the user to enter a stock ticker symbol, and it would go out to the Yahoo Finance website and pull back either the quarterly or annual versions of the income statement, balance sheet, or statement of cash flows for the company. I think I've accomplished that pretty well.

I even did some formatting to make them look similar to those on Yahoo Finance. Here's what the Yahoo Finance version of the Cash Flow statement looks like...


(click on the image above to see a larger version)

And here's the Excel version ...


(click on the image above to see a larger version)

I even did a little "...gathering data...one moment please..." status message while the query is running.


(click on the image above to see a larger version)

This is another example of a web query project that I did where instead of using the CreateObject("MSXML2.ServerXMLHTTP") method, I actually initiate an Internet Explorer session behind the scenes using the CreateObject("InternetExplorer.Application") method. Using this method was preferable for this project, as I was able to identify the table containing the financial statements and pulled the "inner HTML" from it rather than having to use Regular Expressions to tease out each data point.

I thought it was interesting, but being the Excel_Geek, I probably would.

Excel_Geek Insiders subscribers, your file is on its way!

Later,

Excel_Geek

The Amazing Chase is Back!

Hey everyone,

It's that time of year again -- time for the Amazing Chase. This is a super-fun fundraiser, modeled after the television series The Amazing Race, put on by St. Monica's Behvioral Health Services for Women. My team, "Scared Hitless" actually won this last year. Last year we raised over $2,500, which started us in 5th, but we were able to make it up. The competition looks fierce this year, though, so we're gunning for $4,000.

The first leg of the race involves raising money for St. Monica's. The more we raise, the better starting time we get. Here's where you come in. Here's a quick, easy way to support my team. Any little bit you can afford helps. AND, for one lucky donor, I'll throw in a free $50 Project.

Thanks in advance to everyone who helps.

Later,

Excel_Geek

Parsing Google Search Results for URLs

I had a visitor on the blog yesterday who was interested in my old Google search results parsing code. Apparently he found Goohackle's Gooparser and liked what it does -- i.e. return just a clean list of URLs from the search results of Google -- but wanted a way around the limit for the number of results and a way to pull them directly into Excel.

Voila!

I cleaned up the old code for him. The result is a nice, clean worksheet that returns just the URLs from Google search results pages. You can set how many results per page and pages you'd like to return. Be careful, though, as if you hit Google too many times too quickly, they'll start to block you, asking you for your API key, etc. I've built in a random pause (3 to 7 seconds) between the pages in a multi-page query in an effort to prevent this from happening, but no guarantees. Google is a pretty sophisticated outfit, so they may still be able to detect an automated harvesting of data without using the API.

Geoff, enjoy the file. Insiders, the file's on the way.

Later,

Excel_Geek