Nerd Alert: Web Queries of PJ.com
I have an awesome PJ spreadsheet, quite possibly the best one ever. 
But I want to make it better. I have tried to make a web query from excel, so that I can automatically update how many times a song has been played. I've been able to do this one song at a time, but that would take forever to program. So I wanted to query the entire list of songs found here:
http://www.pearljam.com/music/songs
But it just won't work on this page. Any ideas? Anyone have any other cool suggestions for a spreadsheet?
But I want to make it better. I have tried to make a web query from excel, so that I can automatically update how many times a song has been played. I've been able to do this one song at a time, but that would take forever to program. So I wanted to query the entire list of songs found here:http://www.pearljam.com/music/songs
But it just won't work on this page. Any ideas? Anyone have any other cool suggestions for a spreadsheet?
Post edited by Unknown User on
0
Comments
I used to have an Access DB for PJ shows I've attended. I migrated it to a MySQL database on a website. It's good times!
As for querying the PJ DB, I don't think they provide any sort of web service you can call to get the info you want cleanly... or is there?
Are you just getting the whole page HTML and digging out the data from that? If so, you're at the mercy of their current web page layout. They decide to change that up, and your whole scheme go boom.
As for the actual question you asked... I don't know.
It is a spreadsheet, but it does function like a databese. It lists songs down the first column, by album. Then accross the top row it lists the shows I have been to. I basically check the song if it is played at a show. The sheet adds up how many times I have heard each song. I actually code songs differently, depending on the circumstance. For example, I code songs as normal, redux, EV solo, pre-set, tag, accoustic,etc. I can quickly tell you how many times I have heard a song, and how many ways. I store lots more information with it, but I don't want to list it all here.
I thought it would be sweet to be able to compare how many times I have heard a song with how many times it has been played. Or maybe calculate the percentage chance you have to hear a song based on number of times played. I started trying to use the HTML source code, that wasn't happening. Excel has a really simple and powerful way to query web sites. I made it work for just one song, in about 30 seconds, but it won't work on the entire list of songs. I wasn't about to click on every song and write code to import them all in!
8/15/92, 9/28/96, 8/28/98, 8/29/98, 9/18/98, 8/3/00, 8/9/00, 8/10/00, 8/23/00, 8/25/00, 9/1/00, 9/2/00, 4/28/03, 6/18/03, 7/5/03, 7/6/03, 10/1/04, 10/3/05, 6/19/08, 10/27/09, 10/31/09, 5/21/10, 9/3/11, 9/4/11, 10/21/13
More to Come....
The thing about using Excel to query on a song by song basis is it's gonna take you eons. You're going to have to retrieve the page for each song (unless Excel utilizes some sort of cache). That's painful. It might be easier to set up a separate cron or something and have it run every so often to request the page and massage the HTML results into some CSV and store the file locally. Then Excel can query that on demand and it would be much faster.
If you have the query working for a single song, you shouldn't have to write much more code to get it to query *any* song, right, if you just use functions and cell references. It's the slowness that's going to kill you.
While true, pearljam.com does not compare this total side-by-side to MG's personal counts (which is what he wants). It's all about data consolidation.
It's about merging the data on pearljam.com with my data, to create new data. Spreadsheets are fun, especially the more features you add! They are also surprising easy to create and maintain.
I think it would be cool to know the percentage chance that you would hear a song. Well, divide the total number of times a song has been played by the total number of shows. But I don't want to do it by hand and update it night after night as they tour. With a web query, it could do it automatically. Granted, there are other factors that go in to your probability of hearing a song. Take Bushleaguer for example.
People are always making topics about hearing rare songs. So if someone creates a topic about hearing songs played 10 times or less, I have that data readily available. There are lots of uses for this data in my sheet.
The 2 ed solo shows I have seen are included in the sheet. I code those songs differently. For example, when I look at "porch", I see it played a bunch the regular way, I see it accoustic, I see it as a pre-set, I see at as Ed solo, and I see a total times performed.
As for updating, if it is a web query, it will be easy as pushing a button. I manually update the data for a show I see, but that only takes about 3 minutes.