Category: Meta

I created a list of my annual birthday playlists since 2002.

For this process, I used a third party program to extract the data, Excel to format it, and CSS to style it. I’m assuming you’ve used formulas in Excel before so you can plug in the appropriate cells, and have written simple HTML and CSS.

Getting the data

1. I used Exportify to download a .csv file of all my Spotify playlists.
2. Columns included that I used in this process:
   • Track Name,
   • Artist Name,
   • Album Release Date,
   • Album Name,
   • Album Image URL

Track list

I used the CONCATENATE function in Excel to compile the HTML list to paste into WordPress. You could do this all in one step; I did it in multiple steps so I could experiment with showing different things, and so the cell didn’t get crazy long.

1. I created a column with the link to the track:
   =CONCATENATE("<span class=",CHAR(34),"h-cite track",CHAR(34),"><span class=",CHAR(34),"p-name tracktitle",CHAR(34),">",[CELL WITH SONG TITLE],"</span> by <span class=",CHAR(34),"p-author artist bandname",CHAR(34),">",[CELL WITH ARTIST NAME],"</span></span>")
2. I created another column that created the list item that I could paste into WordPress:
   =CONCATENATE("<li>",B2,"</li>")

To keep the page from being super long, I added the track list to the page using the <details> property, which allows it to be clicked on and expanded. Then I used CSS to style “details > summary” to look like a link so people know to expand it.

Adding Microformats

I also included (experimental) microformats based on what I use for my books. Microformats allow other programs to correctly interpret specific types of data, such as a book or (in this case) song citation. No program currently reads microformatted playlists, but I figured better to do it now than wish I had done it later 😉 Once it’s done, the odds I’d go back and update it are low. I picked my own microformats because there is no accepted standard.

The microformats I used were:

• For the whole listing: “h-cite track”
• For the track name: “p-name tracktitle”
• For the artist name: “p-author artist bandname”

Apparently only the h- and p- values will actually be parsed so you could omit the other values.

Because microformats are added as classes, it also gives you an opportunity to style specific parts of the text. I chose to style the track title.

Year data

1. I extracted the year data from their format by adding a column with this formula:
   =TEXT(I2,"YYYY")
2. I calculated the number of songs per year by using COUNTIF:
   =COUNTIF(J$2:J$23,I25)
   where J2 through J23 contains the year data (created in step one), and I25 is a cell with the year in plain text. Adding the $ signs in the source data range is important so you can drag the formula down without it also shifting the source cells.

Graph by year

I followed this tutorial to create a stacked bar graph. Because I wanted multiple graphs on a page, I substituted class instead of id. I also used inline CSS for the grid display properties so I could define a different fractional breakdown for each graph.

Album art

The data included a link to the album art hosted by Spotify’s CDN. I created a column that created the image link with alt text:
=CONCATENATE("<img src=",CHAR(34),[CELL WITH ALBUM ART URL],CHAR(34)," width=",CHAR(34),"50px",CHAR(34)," alt=",CHAR(34),[CELL WITH ALBUM NAME]," by ",[CELL WITH ARTIST NAME],CHAR(34)," />")