Saturday, June 23, 2007

Much ado about Blah-Blah Blah

I'm sitting here with some time on my hands, so I thought I'd get some typing practice on my lovely ergonomic keyboard, which I have just recently rediscovered at my (gasp!) desk. This won't quite be a freewrite session so much as some of my thoughts of late, and what I would like to do about them. Meanwhile I'll be digesting my tasty turkey salad sandwich.

If I don't think about it, I'm a decent touch typist. I can go on for quite a stretch when I don't think about it. Too bad I am at the moment.


I've always enjoyed watching movies in theaters, so much that even though I have an entirely adequate home theater, I still have a hard time staying away from the local cinema. One of the things I'm preparing to tell you about has to do with my desire to go visit the movie theater. I'm not doing it so much to share my unending combination of lazy bastard and geek, but because I think I should document it somewhere so that when you see it later, you'll know I had the idea first.

My job allows me quite a bit of flexibility, and sometimes I choose to take longer lunch breaks than I might under normal circumstances. I used to comb through the movie listings from Google and Yahoo! to find the best movie to watch at the best time, at the best theater. In rather short order, that became too much like work (hey, does that make you wonder what I do? It sure makes me wonder). I decided it would be better to let someone else take care of this heavy lifting for me. Since one of my children is just beginning to read, I didn't feel comfortable giving this responsibility to them yet. I decided to see if I could get my trusty copy of Microsoft Office to do it for me.

My goal was to assemble a list of movies to wach at lunch, sorted by the approximate time of my return to work. I had used Excel in my search for a new HDTV before by using it to grab prices from one of the popular price-search websites, so I was somewhat familiar with using web queries, and manipulating the data from those queries. I started by looking at the data presented by a number of websites. I have still only found one that consistently differentiates AM times from PM times: Yahoo! was the one, so I set out to customize a query for each area theater to download the movies and showtimes on their own worksheet. This seemed to be working well, so I set out to start manipulating the data to assemble the return-drive offset times.

About halfway through this process I began realizing that Yahoo! was Screwing! With! Me! They were throwing ads and switching up the order of the theaters, so that I was not always getting the showtimes for the theaters I thought I was. I figured I would sort that out later as I was starting to work out a good system for converting a long string containing multiple times into multiple time entries with the correct 24hr time format (so I didn't try to watch an 11pm show at 11am). Next I put together another worksheet which would display all of the results. I decided at this point that it might be handy to filter for just a single movie. Yahoo! also had me covered because they could give me a list of just movies, guaranteed to have the same name as the lists of movies at each theaters. Now I had two sort methods to figure out, and a recurring problem with my movie theaters not staying in the same spot.

I decided to rework the whole thing. If yahoo wasn't going to give me consistent results, I had to make a smarter spreadsheet. Instead of grabbing the same webpage 10 times to get one small table I would download it once, then parse each line to sort each theater's shows into their own worksheet, where my previous technique of pulling out the showtimes could be reused.

This gave me an idea for another "feature". There were some theaters I really didn't care to waste my time on, so I grafted in some more creative math to ignore the ones I didn't have an alias or commute time for (duh, because I wasn't going to go there) once the data got copied to the report worksheet. This way it could all be processed the same way, and would be simply hidden from view in the end, maybe not the most efficient for processing, but much simpler for me to design with Excel. I also threw in an option for a second "base" location, with a second set of drive-times. Now I could find out when I might get back to work OR home after seeing a movie. Nice!

Is the current form we have a fairly large spreadsheet, that can retrieve the first 20 showings of up to 20 movies each at up to 10 theaters closest to (up to) two locations. It sorts ALL of this data by proximity (represented by the number of minutes you think it takes for you to drive between your "base" and each theater) added to the calculated ending time (start time plus end time plus 15 minutes for commercials and previews). Alternatively it can show you a single movie's showtimes, in case you already happen to know what you want to watch, but don't know where to watch it. Two to four clicks is all it takes.

I'm rather proud of it, but I recently decided it wasn't enough. There seems to be a boom in web creativity as more content owners make it easier to play with their information, or to add your own to theirs (Hi, Google). I would love to have something like this on the web to look at from anywhere, but nobody seems to have done it yet. I guess it's gonna be me for now. Man, are you gonna be sorry for not doing all this hard work for me.

I've got another thing I really want this to do. I want it to pick a showing, then add it to my calendar! Why? This started out as an exercise in not missing important things at work. If I can put it on my calendar, it would be eaiser to see where there might be any conflicts with other items on my schedule. Right now I use two calendars: Outlook/Exchange for work, and Google for not-work. I sync my phone with Outlook, and get alerts from Google Calendar via SMS. To begin with I'm going for Outlook "integration" to leverage the work I have already done, and my relative ease with using VBA. The kicker is that I would like to integrate on the Outlook side so that I can preview the movie schedule right next to my current calendar, select the item, and slurp it into my own calendar. I haven't come close to playing on this level before with Outlook. The less elegant method (in my mind, anyway) would be to use Excel to grab the calendar from Outlook and do the picking from Excel, then send it back over to Outlook. It may end up being easier to implement, though.

Once I have the kinks worked out, and a clearer vision of how it works, my goal is to make my own gadget for Google Desktop/Home-Page/Etc. that would look at your location, ask what movie theaters you do or don't like, automatically calculate drive times and store that info for future use, show you the movies coming up (maybe for the next few days) and send the appointment to the mail/calendar app of your choice after you pick the movie.

That would rock.

I think I've exercised the finger enough now. More "free writing" later!

Blogged with Flock

No comments:

Post a Comment