01438 741177         thewinesociety.com

The Society's Community

The spreadsheet thread

So… as a first step I tried to specify what useful fields would be and how they might be used. Here is a google sheet:

The following have shown interest in this so getting a call out to ask for feedback.
@adbdorset
@piersb
@Leah
@strawpig
@Alchemist
@SteveSlatcher
@andrew73

Bear in mind that this is “just” to extract data on Reserves and En Primeur from the society (so not detailed description of tasting, ratings, etc). I guess it would also be possible to add a category that shows “delivered” as well…

5 Likes

Being self critical… I guess we could remove the “drink status” field as that can be computed. Same with price per bottle (price paid for group/bottles stored).

1 Like

This is a great thread. I never knew I could download a csv of my reserves.

I would be happy to join the feedback list.

Added to the list…

1 Like

Having for the first time ordered a 12 bottle mixed case from reserves, I now realise that we need a column for the initial order (say 6 bottles) vs currently remaining from the order (4 bottles). I’ll update the googlesheet. All comments welcome.

I use neither reserves nor spreadsheets, so these are mere suggestions, rather than desires. Feel free to adopt them or not as you see fit.

  1. Change “Bottle Type” to “Bottle Size” - in cl or ml. That way the size of the “Other” bottles can be coded. Let the receiver of the exported data convert it into text if they want.

  2. “Product Type” is a bit vague. “Madeira” is not a “Product Type” in the same way that “Red” is. It is arguably more of a region. How about a “Colour” column (don’t forget Orange!), and one for “Still/Sparkling/Fortified”?

  3. Is “Region” just free text? Maybe it should be restricted to a number of fixed options, but I realise that might be impractical. Personally I would prefer a column for country; and a separate one that, where applicable, only holds the PDO/PGI for EU wines, or one of the official non-EU regions (pobably also impractical)

I’m not going to argue the case for these suggections, but if anyone has any questions I’m happy to try to anwser. Probably best to reply to this post or tag me, as I might not notice otherwise. Good luck with the project, @Brighton_Ben!

@SteveSlatcher - your suggestions duplicates Cellartracker structure :slight_smile:

The limiting of entries to PDO/PGI/ etc is enforced on CT.

P’raps if people don’t want to use CT they could create one entry on CT to download it as spreadsheet then all the fields used on CT would be column headers ?

Maybe CT has changed since I last saw it - I don’t use it myself, but I did check out the database schema several years back. Any similarity was coincidental, or a case of “great minds think alike” :slight_smile:

I seem to remember they had country, region, sub-region and sub-sub-region, but it was never clear to me how the regions and sub-regions should be used, and they seemed to put PDO/PGIs into one of those fields

Thanks Steve! The Bottle size in ml would help. It will depend on whether TWS has that data in their database structure at the moment.
Likewise for product type - whatever they have would work

Since the aim is to get the WS data they have into a more useable form without having to create new data I doubt we will have the PDO/PGI etc… but I could be wrong. Maybe they have.

1 Like

I said this about my fishing diaries 20 years ago…no closer.

LOL me too.

My mountaineering diaries the same (not to mention the slides!)

You need to make it at least xlsx.

That is, if it’s for really large cellars.

A JSON schema is surely the best option, though? (Hides from ensuing flame war…)

7 Likes

Managed to add all my En Primeur wine into an Airtable sheet, quite happy with it!

I have sheet for the bottles which are linked to the offers

9 Likes

Oooh. What’s an Airtable? Do you have to manually link the descriptions or is that automated somehow?

Airtable is an online database tool, really versatile. I use it for all sorts

Sadly it’s all manually inputted but once it’s on you can link to other sheets

For example I have the EP offers linked to each bottle of wine

So didn’t have to duplicate the descriptions

So… I finally got around to emailing the nice IS/IT people in at the Wine Society with the fields and extract suggestions… They were invited to have a look at this thread as well.
No replies yet but will keep you posted. Or maybe they can post directly here.

2 Likes

Just read through some of this thread with some interest. I love a good spreadsheet…

Mine is absurdly complicated but I thought I would just share how I use it to keep track of what is in my ancient Eurocave. It it is no more specific than what shelf it is on, I know some people like to put an exact location in but around here that would last about a week. There is some method for putting the bottles with longer drinking windows at the back, but it is not hard and fast as almost everything stored at home is ready.

Anyway, one tab of this spreadsheet is in the “shape” of my eurocave shelves. It pulls in wines from the longer list (essentially a data tab) that I code up as being in there, and assigns them to the correct shelf based on some high level indexing a la “red hermitage”, “white chalon” etc via a series of lengthy “sumif” formulas. This also shows me how much capacity there is on any given shelf, ie the white burgundy section is three high and has space for 3x11=33 bottles, and so my spreadsheet indicates that there are currently 31 and that my free space is 2. Whenever I get to a number mostly divisible by a case size on a more than 1 shelf I know it is time for a reserves withdrawal. The issue with this is that anything new arriving from the Wine Society needs to be buried at the back/bottom of each area which generally necessitates taking everything out and starting again. It looks like this.

I just print a copy of this out and stick it on the sliding shelf. I supplement it with a copy of the “Restaurant Style Wine List” from Cellartracker which I also chuck in there. Prior to Cellartracker this approach also served to show me how many bottles of each specific appellation I had versus what I considered a balanced cellar to be based on 1000 bottles (which in turn was a rip-off from a very old Guide Hachette). So I was able to see if had too many bottles of Claret and not enough bottles of Chablis and so on.

If I am honest it is a bit of a pain maintaining both my spreadsheet and Cellartracker, and I have been thinking that if I started to become cleverer in my use of “bins” in the latter I could probably do away with the Eurocave section of the spreadsheet. Then again I have also built other functionality such as being able to work out duty and tax, and the appropriate cost of storage from when a wine went in to when I took it out and so on, so perhaps there is still a need. Might share that later as well as doing it allowed me to see that buying Sauternes EP hasn’t been the greatest idea I’ve ever had!

9 Likes