How I use Google Spreadsheet and iTunes Ratings to track DR scores - AVS Forum
Forum Jump: 
 
Thread Tools
post #1 of 4 Old 08-15-2014, 11:02 AM - Thread Starter
AVS Special Member
 
Gecko85's Avatar
 
Join Date: Aug 2005
Location: California
Posts: 2,229
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 153 Post(s)
Liked: 95
How I use Google Spreadsheet and iTunes Ratings to track DR scores

First, I will be the first to admit none of what I'm about to describe is necessary at all...but I was bored.

I got on a kick of cataloging the DR (Dynamic Range) score for all my CDs, using the http://dr.loudness-war.info/ website. Most were already listed, so it was just a matter of looking them up and making sure I was looking at the correct release. Some weren't there, so I would run the offline meter and upload the report to the website. Again, no real reason for this other than I was bored.

But then I had an idea: what if I used the 5-star rating system in iTunes to "rate" each album by its DR score? I could then, if I wanted, created smart playlists by album rating. Could be fun. But how to map the 20 point DR scale to the 5 point iTunes ratings? Duh - just divide by 4! Well, that *would* work, but I wanted a bit more custom mapping. I wanted a rating of Zero through 5, not 1 through 5. Google Spreadsheet to the rescue!

First, my new scale would look like this:

DR score of 0-3 = iTunes rank of 0
DR 4-5 = 1
DR 6-8 = 2
DR 9-11 = 3
DR 12-13 = 4
DR 14+ = 5

Somewhat arbitrary, but that's what I decided on.

So, in the spreadsheet, I first used conditional formatting to color code the DR scores (so that it's pretty!) I then used a convoluted formula that took me a while to figure out to map the DR score to the iTunes rank. Here's the formula:

Quote:
=IF(E3<4, 0, IF(E3<6, 1, IF(E3<9, 2, IF(E3<12, 3, IF(E3<14, 4, IF(E3>=14, 5,))))))
Here's what the columns look like in the spreadsheet:



And here's some of the conditional formatting rules to do the color coding:



And here's the formula again (for column G), as it looks in Google Spreadsheets:



I'm now in the process of "rating" each album in iTunes. I shade column G gray once I've rated the album in iTunes, so I know what's been done. I have a long way to go.

Panasonic TC-P60ST60, Pioneer SC-1523-K, Oppo BDP-103D, Pioneer PL-550 + Cambridge Audio Azur 640P, B&W CM1 (fronts), B&W CM Centre, Athena Point 5 Mk II (rears), Hsu VTF-2
----------------------------------------
Sony 34XBR960, Onkyo TX-NR414, Sony PS3, Athena Point 5 Mk II (center and fronts), Wharfedale WH-2 (rears), Polk PSW10
Gecko85 is online now  
Sponsored Links
Advertisement
 
post #2 of 4 Old 08-15-2014, 02:41 PM - Thread Starter
AVS Special Member
 
Gecko85's Avatar
 
Join Date: Aug 2005
Location: California
Posts: 2,229
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 153 Post(s)
Liked: 95
Oh, and maybe one day if I'm *really* bored I'll rate them down to the individual track level.

Who am I kidding...never going to happen.

Panasonic TC-P60ST60, Pioneer SC-1523-K, Oppo BDP-103D, Pioneer PL-550 + Cambridge Audio Azur 640P, B&W CM1 (fronts), B&W CM Centre, Athena Point 5 Mk II (rears), Hsu VTF-2
----------------------------------------
Sony 34XBR960, Onkyo TX-NR414, Sony PS3, Athena Point 5 Mk II (center and fronts), Wharfedale WH-2 (rears), Polk PSW10
Gecko85 is online now  
post #3 of 4 Old 08-16-2014, 07:38 AM
Moderator
 
PooperScooper's Avatar
 
Join Date: Dec 2002
Location: Leominster MA
Posts: 19,938
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 68 Post(s)
Liked: 274
Pretty cool, Gecko! It's too bad that the DR data is sometimes incomplete wrt identifying the actual CD (or whatever digital format) of the multiple releases. Many times the release year is good enough, but not always. It would be nice to be able to automate the retrieval of the DR database info for a CD. Although the number of heuristics needed would probably lead to project abandonment after a short period of time even if the necessary data was guaranteed to be in the database. I use the database quite often and sometimes like to guess what the DR values (in general) are after listening to music I haven't looked up on the site before.

larry

Opportunity is missed by most people because it is dressed in overalls and looks like work. -- Thomas Alva Edison
PooperScooper is offline  
post #4 of 4 Old 08-16-2014, 08:17 AM - Thread Starter
AVS Special Member
 
Gecko85's Avatar
 
Join Date: Aug 2005
Location: California
Posts: 2,229
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 153 Post(s)
Liked: 95
Quote:
Originally Posted by PooperScooper View Post
Pretty cool, Gecko! It's too bad that the DR data is sometimes incomplete wrt identifying the actual CD (or whatever digital format) of the multiple releases. Many times the release year is good enough, but not always.

larry
Yeah, I start by looking up a title manually and look for entries for my release year, and *usually* someone will have put in the UPC code so I'll know it's a match. If I'm ever not sure, I just run the offline meter to verify. I don't understand why people submit without filling out the rest of the info on the form. It only takes a minute...

Panasonic TC-P60ST60, Pioneer SC-1523-K, Oppo BDP-103D, Pioneer PL-550 + Cambridge Audio Azur 640P, B&W CM1 (fronts), B&W CM Centre, Athena Point 5 Mk II (rears), Hsu VTF-2
----------------------------------------
Sony 34XBR960, Onkyo TX-NR414, Sony PS3, Athena Point 5 Mk II (center and fronts), Wharfedale WH-2 (rears), Polk PSW10
Gecko85 is online now  
Reply 2-Channel Audio

Thread Tools
Show Printable Version Show Printable Version
Email this Page Email this Page


Forum Jump: 

Posting Rules  
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off