Hugo & Nebula Awards: Spreadsheets and more!

[Atlanta]

Yeah what is this months challenge? Timescale?

[Art, Stay home, stay safe.]

February is almost over, so our monthly reads will be resetting in two days.

For March we have Cyteen and God's War lined up as upcoming reads.

As for Challenges, we have an active Dune Chronicles challenge. It will end on March 31st.

As for April challenge, we are still in the voting stage and it looks like Imperial Radch (Ancillary Whatchamacallits) series is leading one vote ahead of Sprawl and Mars Trilogy.

[Bryan, Village Idiot]

I'm just happy everyone is taking as much pleasure with keeping tracking of their progress as I am!

I made these other spreadsheets to keep track of how much I've read. It basically gives you more data to play with and that makes me happy. I'm more than happy to let anyone use it! There is one for audiobooks and one for traditional books.

More spreadsheets!

[Bryan, Village Idiot]

I've updated the spreadsheet with the 2019 Hugo nominees. I also made a small change. The winners of each year are now bold to help them stand out a bit.

[Kateblue, 2nd star to the right and straight on til morning]

Thanks, Bryan. I'm always so glad that you are updating the spreadsheet because I know I would probably goof it up despite lots of Excel experience.

[Oleksandr, a.k.a. Acorn]

Thanks, Bryan!

[Allan]

I've done so much customizing that my spreadsheet barely resembles Bryan's anymore. Pretty nerdy, I've got counts, stats & sorts built in. Although I should post it in case anyone wants to steal pieces of it.

[Bryan, Village Idiot]

Allan wrote: "Although I should post it in case anyone wants to steal pieces of it."

You should! I would be interested seeing it! I love random data.

[Bryan, Village Idiot]

Also, I didn't add the retro to the spreadsheet with my last update. I have added it now.

[Abigail]

Wow!! I’m pretty new to this group and I’m in awe of the spreadsheets. How exciting! Thanks so much.

[Art, Stay home, stay safe.]

Abigail wrote: "Wow!! I’m pretty new to this group and I’m in awe of the spreadsheets. How exciting! Thanks so much."

Welcome to the group once more, Abigail! Hope you will be joining us in picking and discussing our Monthly Reads, look out for our Nomination Threads where you can throw in your own picks.

[Art, Stay home, stay safe.]

It's been a while since I've updated my spreadsheet with read books, so imagine my surprise when I saw that I am only .2% from joining the double digit club!
9.8% and that is not including Ancillary challenge and Genocides I am working on right now. I am tempted to try and break my personal record this year, but it's too early to say anything.

[Sarah]

I have an ungodly love of spreadsheets. If my total ever goes beyond a meagre single-figure percentage, I want to add a bunch of graphs.

I'm tracking them not with an 'x', but with my star rating, so I can use those later. I'm interested to see my average rating per year, % of winners read (as opposed to % of nominees), graphs of completion per year, and so on.

[Art, Stay home, stay safe.]

Sarah wrote: "I have an ungodly love of spreadsheets. If my total ever goes beyond a meagre single-figure percentage, I want to add a bunch of graphs.

I'm tracking them not with an 'x', but with my star rating,..."

You'll fit right in, we are pretty down to earth folk. I am constantly relying on posts made by other members because I miss the most obvious thing that stares me right in the face.

In any case, pleasure having you with us.

[Allan]

Sarah, I'll see if I can post my customized version of the spreadsheet. I'm a heavy number cruncher and a spreadsheet geek so I probably have a start on many things you want to do. I kept my own list prior to getting Bryan's, where I built a composite top 100 to read from, and where I started tracking every book I read with page counts, stats like pages per day/month, etc. (I've posted before that reading 40 pp/day will net you about 50 books in a year, a little shy of 10% of the list). On joining this group about a year ago, I integrated Bryan's list and added color coding to indicate read, owned and available through library sources. I have lists of just the winners and complete alphabetical lists by title and author for easy lookups. I have counts of read & owned by year and collect them into decades for charting. I also have a tab where I track group reads and challenges on the Worlds Without End site (which I recommend everyone check out). I'm not recommending it over Bryan's because it's not polished but feel free to steal anything you like.

[Bryan, Village Idiot]

man, it is so nice to be among fellow data nerds! Maybe we can all post our spreadsheets in a single place so we can show off and get some new ideas for our own spreadsheets? If you are interested in that, I'll set up a Google Drive spot where everyone can post and delete.

[Allan]

Sounds like a great solution!

[Sarah]

Yeah let's do it. I sent mine over to Bryan, as he seems to be the guardian of the sheets.

Allan wrote: "on the Worlds Without End site (which I recommend everyone check out)."

It won't load for me. Hopefully I can catch it when it's back online.

[Allan]

Yes, I had trouble getting to the site today too. Down for maintenance I'd guess.

[Bryan, Village Idiot]

Guardian of the Sheets? I'm good with that. I've created a Google Drive folder for anyone to put their spreadsheets on it.

I'm not a lawyer or anything of that nature, but I think it's worth saying to prevent any future upsets. If you are using the drive to share you spreadsheet, then you are allowing your spreadsheet to be copied by anyone who has a link to our drive. If you are ok with that, then lets have fun. If not, then only upload what you are comfortable with.

Here is the link. Open Drive for our Group

[Kateblue, 2nd star to the right and straight on til morning]

Hey, Bri. I can't get this link "Open Drive for our Group" to work.

It's probably my fault

[Sarah]

It looks like goodreads blocks drive links. Argh.

Can you put the link into a google doc?

[Bryan, Village Idiot]

lets try it again. second try for the win?

[Kateblue, 2nd star to the right and straight on til morning]

Works, and now I have bookmarked the location, so I am good

I'm not posting mine because I have don't nothing to it.

[Sarah]

Neat, it works now. I've shared mine with the changes I made so far.

Bryan, what's the stuff at the bottom of your H&N list?

[Allan]

I posted mine. Steal freely! Sarah, I saw your groupings by decade - I've got something similar on the last tab where Bryan's list is, between the title sort and the author sort, with a little chart of % read by decade and % I own and have not read.

[Sarah]

Allan - your master list looks great. I'm assuming you took average rankings from various lists to create a best-of/reading priority list? How did you compile the numbers though? I can't see the formulas. Did you do it manually?

[Sarah]

Bryan - I like your page tracking stats. It makes some really nice data. Perhaps it's something we can add to the main data sheet so we can get some automated stats. It'd just need a 'date read' column and a '# of pages' column.

I'll add it to my own copy and play around with it.

I realise this is data-for-the-sake-of-data territory but it seems like you're all on board. A basic pages/month already exists on goodreads of course, but it's quite limited.

[Allan]

It's been a couple of years but yes, that's the basic concept. The Base tab is the lists I used, then I combined and sorted them to see the books on multiple lists. Then, for the books on all three lists, I averaged the rankings - I must have taken out the formulae at some point, don't recall why. Also on the Master tab is the ranked list of all the books on two OR three lists. Purple indicates "read" and the shade shows when I read it, darkest being this year. I did all this before I found this group and got the full H/N list to work from.

I didn't have a chance to look at Bryan's page counter closely but I have my "Read" tab tracker, which allows me lay out monthly what I'm planning to read and see where I am with goals.

[Art, Stay home, stay safe.]

I've added a link of the GoogleDrive to the original post in this thread, so that there's no need to look it up in future.

[Kateblue, 2nd star to the right and straight on til morning]

thanks Art

[Bryan, Village Idiot]

Hey everyone. Sorry I had a busy weekend.

Allan, your spreadsheet is intense. I'm going to have to take some time to look at it more carefully.

Sarah, I'm trying to look at yours on my work computer, but it's not having it. I'll have to look at it tonight when I get home. I'm excited to see what yours looks like.

To answer your questions:
Sarah wrote: "Bryan, what's the stuff at the bottom of your H&N list?"

The stuff at the bottom of my H&N list is my thrown together attempt to see how long it is going to take me to read all of the books on the list. I'm hoping that I can get an average of how many books are added to the list and how many I am reading each year. once I have that, I'll be able to get a rough guess...I'm looking at it now and I'm realizing I made a mistake. The way I have it set up I'm adding each book twice. I'll have to mess with that.

Sarah wrote: "I realise this is data-for-the-sake-of-data territory but it seems like you're all on board. A basic pages/month already exists on goodreads of course, but it's quite limited."

I am all on board and I think it would be a lot of fun to collaborate together (You, me, and Allan...and anyone else that might want to nerd out with us) to make a Master data sheet or at least get more than just my data, but have someone to compare it to.

Also, I added 3 spreadsheets to our group drive. 1 is the one I'm using, so it has all my stuff in it. The other 2 are blanks of what I use. I have one set up for audio reading and the other for traditional reading. My wife doesn't like to be read to and she was interested in what I was doing...but she got bored of it pretty quickly.

[Allan]

Bryan, all those "list" tabs are just my backup work for compiling my master list - pre-HN group, pre-using WWE extensively. The "Read" tab is where it gets a little more intense, but that's just my version of a book and page tracker. The monthly stuff off to the right is just something I tried but abandoned. Then there's your list and the sorts I did on that. There's a bunch of smaller tables and little things I've experimented with; it's just a place to mess around with ideas.

I use color coding in nearly all my spreadsheets. It's just easier to pick things out quickly. Where I haven't read a book, the color indicates whether I've found it and where.

Regarding book counts, I modified your summary table at the bottom of your list, added a column for counting unread books on the shelf, then took a snapshot of it at the end of 2018. That allows me to see what I've done this year. Maybe that helps in what you're trying to do?

[Sarah]

Allan wrote: "The Base tab is the lists I used, then I combined and sorted them to see the books on multiple lists..."
Nice work! It might be helpful to have that summarised in its own document, as something that new members can refer to.

A lot of this is quite idiosyncratic (especially the colour coding) so I'm not sure what to do with it. I do like colour-coding, but only when it's automated.

I also didn't consider to track book ownership, but I see that you have. Do you think that is something most members would want?

Art wrote: "I've added a link of the GoogleDrive to the original post in this thread, so that there's no need to look it up in future. "
Awesome; thanks Art :)

Bryan wrote: "I'm trying to look at yours on my work computer, but it's not having it. I'll have to look at it tonight when I get home.."
That's unfortunate. It's just a basic Google Sheet so I don't know what the problem might be. Did you get it to work at home?

Bryan wrote: "The stuff at the bottom of my H&N list is my thrown together attempt to see how long it is going to take me to read all of the books on the list. I'm hoping that I can get an average..."
I like that idea. I'm adding a '# of pages column' to your original sheet layout, so once I've done with inputting the info, I can look at automating some similar projections. After adding half those page numbers already, I am starting to appreciate what a mammoth task it is to read everything on the list, especially with about 10 new books every year ;)

Bryan wrote: "I am all on board and I think it would be a lot of fun to collaborate together [...] to make a Master data sheet"
Yes I like this idea. I am making mine with a mind that I won't be the only one using it, hence I want to automate as much as possible. The idea is that someone would only need to edit the bookdata once (per book), and everything else just fills itself in. They would obviously just need to extend the table for new books each year, but it should be pretty low-maintenance.

[Allan]

Ownership tracking: it's really so that I know what books I've found, and where to go when I decide to read it. There are 579 books; it's easy to mix up titles or just plain forget what you have. Some are hard to find too, and it helps flag those. Yellow indicates that it's on my shelf, phone Kindle or computer (epdf files). You could break those apart. Orange is what I've found at the library, so I know I don't have to buy them. I haven't checked library access for everything but I mark them if I'm browsing.

If you want it automated, you could insert the ownership column as I have and standardize color-code by letter, i.e. S=Yellow=shelf, L=orange=library, K=blue=kindle library, etc. or whatever flag you want to use.

[Allan]

Red means I'm reading it now, so source becomes irrelevant, then it changes to purple when I'm done. The purple shades indicate whether I read it this year, last year, or before that. The colors are consistent throughout the file.

[Allan]

Oh, and the sorted lists are for quick-finding (is that a GED spell?). Rather than sort through your chronological list, I can find a book quicker by title or author and just look up the year.

[Allan]

Sorry, I keep forgetting stuff I mean to say, but I'm very willing to help out with spreadsheet mods if you need it. Another thought is that we could use dropdowns or push buttons to make changing book status easy for non-technical users.

[Sarah]

Allan wrote: "Oh, and the sorted lists are for quick-finding (is that a GED spell?). Rather than sort through your chronological list, I can find a book quicker by title or author and just look up the year."

I might be misunderstanding what you mean, but wouldn't it be easier to make a sorting filter on your chronological list, and just use that?

So you are doing the colour-coding all manually? You could streamline that with conditional formatting.

Allan wrote: "Another thought is that we could use dropdowns or push buttons to make changing book status easy for non-technical users."
Yeah that's what I'm working on atm. What do you think? LINK

[Bryan, Village Idiot]

Sarah wrote: "So you are doing the colour-coding all manually? You could streamline that with conditional formatting."

I was thinking the same thing. I use conditional formatting a lot with the spreadsheets I use for work. Unfortunately I can't share those due to confidentiality.

"colour", are you British?

Sarah wrote: "That's unfortunate. It's just a basic Google Sheet so I don't know what the problem might be. Did you get it to work at home?"

Yes, I got it to work. It looks good, but it saved as a picture rather than an interactive spreadsheet. I'm sure I'm doing something wrong.

Sarah wrote: "I am making mine with a mind that I won't be the only one using it, hence I want to automate as much as possible. The idea is that someone would only need to edit the bookdata once (per book), and everything else just fills itself in."

I like this idea...you've got my brain working on how to get this done...I might have to start working on that.

[Allan]

I've done all the color-coding manually, mainly just because the worksheet grew up piecemeal and book status doesn't change that fast. No reason it couldn't be conditional if you put in the flags and columns you need.
For general use, that would be best.

I did the author/title sorts separately because I wanted to leave Bryan's spreadsheet and formatting alone, but for color-coding. I really only use them for quick finds; sorting and re-sorting the main list would be a pain unless you put quick-sort push buttons in.

My "Read" spreadsheet includes whatever off-list books I read as well, so you have to have a place to put those if you want full page count.

Check out my decade chart too (between the sorts).

[Sarah]

Bryan wrote: "it saved as a picture rather than an interactive spreadsheet."
Strange. It could be that it converted to the wrong file type when you downloaded it. You don't actually need to download it; you can open it in google sheets in a browser. That might help.

And yes, I am British :)

Allan wrote: "Check out my decade chart too"
I remember seeing it when I first checked out your spreadsheet, but now I can't find it haha. Which tab is it in?

[Allan]

The last tab, with Bryan's list. To the right are my sorted lists, and it sits between them at the top.

[Bryan, Village Idiot]

These are awesome. I've been busy at work, but I really enjoy looking at your guys' spreadsheets and seeing how you like to separate out the data.

[Kateblue, 2nd star to the right and straight on til morning]

My compliments to everyone about these spreadsheets. Wow!

[Benny]

Bryan wrote: "Update*

Hey everyone, I just added the 2018 nominees to the spreadsheet."

Great spreadsheet. I am at 1 %. Still, have to check some translation into my native language(Dutch) and look up the original titles to further complete the sheet and drive up that percentage. Thanks, Bryan.

[Bryan, Village Idiot]

Benny wrote: "Great spreadsheet. I am at 1 %. Still, have to check some translation into my native language(Dutch) and look up the original titles to further complete the sheet and drive up that percentage. Thanks, Bryan."

Thanks Benny! If I come across a Dutch translation of the books, I'll let you know!

[Allan]

I was just giving your sheet another look, Sarah. I think the best stat is "hours left to read." I love it, it sounds kind of like a Twilight Zone death clock. I've also given your comments in my sheet some thought as well, and I might play around with color-coding and quick sorts on one alphabetized table. I'm going to use pastel colors to designate source of "unread but found" books, I.e. Yellow = owned, orange = library, green = online library, but stick to my purple shades for read books. I'm in the process of adding page counts for the editions I have. I'll stay with my "Read" page, as I include non-listed books there and can manage timing and page counts there. Thanks for giving me some good ideas!

[Allan]

Working on the next iteration of my worksheet. I went through the entire list, looking for sources and documenting them. I've read 138, and between my physical bookshelf at home, the Kindle app on my phone, and PDF files I've downloaded from epdf, there's another 154. Library sources (Open Library and my local city library) account for 176 more. I'm left with a list of 111 books that I couldn't find. Open Library is a gold mine; I'm only counting books that I haven't read or have in my possession.

I'm busy building this into my list, along with page counts. I do have enough data in to calculate average pages by year, so I've inserted a chart that proves definitively that book length gradually increases from about 200 pages in the mid-60's to 400-ish in 2000, leveling off the last couple decades.

I'll post it as soon as I finish. I've just been debating on how to build it, considering my sorted lists and and color coding.

[Rebecca]

Allan wrote: "I was just giving your sheet another look, Sarah. I think the best stat is "hours left to read." I love it, it sounds kind of like a Twilight Zone death clock. I've also given your comments in my s..."

Much like my GPS, I see that as a challenge/time to beat.