Birthday playlists

I’m a book lover, sci-fi writer, and native plant nerd. Learn more about me, and see what I’m up to now. This is my personal site — you may be looking for my professional sustainability consulting services. Explore my site Join me in pondering my big questions (my approach to organizing my learning and thinking…

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

I used Exportify to download a .csv file of all my Spotify playlists.
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.

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>")
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

I extracted the year data from their format by adding a column with this formula:
=TEXT(I2,"YYYY")
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)," />")

I tried out weeknotes this summer and fall over on my blog, but I’m not sure that’s the right place for them — going to try out posting them here on Fridays.
Stuff I did this week

spent 9.5 hours on my novel — worked on outlining the next four chapters, which make up one big, very important sequence at the midpoint
attended a 2-day virtual conference — two half days is a nice format
published my birthday playlists on my website
updated my RSS feed and newsletter subscriptions — when I finished, I counted my subscriptions by the gender of the author and was pleasantly surprised that the breakdown was pretty close — ignoring multi-author and anonymous authors, I follow 51 blogs by women and nonbinary authors and 58 written by men
took the weekend off working / writing
walked with a friend
got my teeth cleaned
used Teladoc to get a 3-month prescription for my birth control, so I have a countdown to find a new PCP

Reading

read The Long Game, realized it was a sequel, then read the first book Heated Rivalry
started reading Ducks by Kate Beaton
still reading Rest is Resistance by Tricia Hersey and The 12 Week Year for Writers by Trevor Thrall
added 19 books to my TBR based on end of year recs

Website changes

added a land acknowledgment to my about page
changed the background color from tan to a soft pink
updated and reorganized the menu — added a writing heading
created a page to collect pages on this site
moved my listening stats onto a page for the year

Replied to 100 things you can do on your personal website by an author (jamesg.blog)

One of my favourite things to do in my free time is to tinker with this website. Indeed, this website is the culmination of years of tinkering.

James has a fun collection of ideas of things to do on one’s website, some of which I have done and some of which I have not. Here are a few more things I have on my website that weren’t on the list: Yearly listening pages to supplement last.fm’s tracking Recreated playlists A page listing…