Master Excel Winemaking Spreadsheet

[mendozer]

Hello fellow wine-os. I've been an amateur winemaker for about 8 years (took the last two off) and this year I've decided to finally get my logistical sh**t together. I mainly use Daniel Pambianchi's book and various online calculators to help me through the process, FermCalc, some Winemakermag calculators, etc. Hell even my Beersmith software has a sulfite calculator to help me out. But because I only make wine in the fall with fresh grapes, it's easy to forget the little things in a year. I would like to make a master excel spreadsheet with everything on it. So every year I'd open a new one, enter my volumes, pH, TA, free SO2, etc and have the other sheets on the file perform the background calculations so I don't have to jump around to different resources. Excel is a powerful calculating software and it's under-appreciated. Basically I want to create a winemaking "software" with the software we all have (and if you don't have Microsoft, OpenOffice has a similar version free and open source).

so...my question for all of you is where to find these formulae and if someone already started this I would love to modify it to my needs.

I'm looking to have it do the following:
pH corrections
TA corrections
SG/Brix into potential alcohol calcs
Sulfite additions
Blending calculations
fortification for ports and such



If there are any other calculations you feel are important by all means chime in!

 

[wood1954]

Chapatalation? How much sugar to get to a desired SG, if you can pull all those formulas together please share them. There are plenty of websites that have those individual formulas but not in Excel

 

[sour_grapes]

fortification for ports and such

There are two possible fortification calculations you could be speaking of. One is a simple Pearson square, which would guide you in adding a spirit to fortify a finished wine to the ABV of your liking. (I can give you my Excel sheet to calculate that if you wish.) A more complicated question is making a port the traditional way, i.e., adding a spirit to a wine before it is finished fermenting. This will stop the fermentation, and leave some residual sugar for the port. For that, I worked out the calculations for when during the fermentation (i.e., at what Brix/SG) to add the spirit. Here are two posts from a thread where we discussed this, including a link to the Excel sheet I wrote for this.

Umm, get ready to tip. I hate to tell you, though, I didn't have to pull out any big guns; it only required algebra. For a minute, I thought it was going to turn into a quadratic equation, but it didn't even do that.

I built off the excellent foundation you laid.
Parameters:
volume_wine (in liters)
rho_i_wine (initial g/l sugar)
rho_port (desired g/l in port)
ABV_port (desired ABV of port)
ABV_brandy (I am using "brandy" as my spirit!)
(derived quantity: ABV_wine=(rho_i_wine - rho_f_wine)/20, as per Seth)

Unknowns
volume_brandy
rho_f_wine (final g/l sugar in wine)

2 independent equations

(1, the sugar equation) rho_port = rho_f_wine*volume_wine/(volume_wine + volume_brandy)

(2, the alcohol equation)
ABV_port= (volume_wine*ABV_wine + volume_brandy*ABV_brandy) / (volume_wine + volume_brandy)

I solved Eqn 1 for rho_f_wine to find:
rho_f_wine = rho_port*(volume_wine + volume_brandy)/volume_wine ,

then I shoved that into Eqn. 2 (after substituting your derived quantity for ABV_wine). You can easily, i.e., algebraically solve for volume_brandy.

The result is:


volume_brandy = {ABV_port - (rho_i_wine - rho_port)/20} / (ABV_brandy - ABV_port - rho_port/20)


This is the desired volume of brandy in liters.

You then substitute that value into Eqn. 1 to find rho_f_wine.

The result is:


rho_f_wine = (ABV_brandy - rho_i_wine/20) / (ABV_brandy - ABV_port - rho_port/20)


Remember, this is the residual sugar that you want (in g/l) at the time you should fortify to stop fermentation.

I did check that this gives the same result as your MatLab script. (I didn't compile it, I just ran it on MatLab.)

You can either use ABV as a number between 0 and 100 and use rho in g/l, or you can use ABV as a fraction (number between 0 and 1) and use rho (in g/l)/100; this comes from the fermentation conversion of rho/20, which provides the ABV as a percentage, so you have to divide by another 100 to get ABV to a fraction instead of percentage. I think most people would be best served to use ABV as a number from 0 to 100, and sugar in g/l.

For all you people who would prefer to use SG rather than g/l of sugar, the conversion is close to:

SG = 1 + rho/2644 where rho is in g/l,

Or, of course, rho = (SG-1)*2644.

HTH, John et alii

Well, I put this into an Excel sheet, which you are welcome to (if I can figure out how to attach it). There are two sheets: one you input the sugar in g/l, the other you input the SG of the must instead. In both sheets, you input parameters in the yellow boxes, and the answers come out in the blue boxes.

View attachment 14828

 

[Rice_Guy]

* specific gravity numbers are linear against percentage, it is basically a mass balance where you are targeting 30% +/-
* pH is trial and error, we don’t really know what the buffering capacity is (ionic make up)
* TA also works as a linear mass balance, ,, volume times percent TA plus B equals volume times target C for additions,
* blending and fortification are Pearson’s square
* volume changes with sugar I pull off a table, it should be a K times the kilos dry matter if a formula

 

[mendozer]

awesome feedback. I ran into an issue this morning putting in sulfite calculations. I'm fairly confident my formula is right, but it's not coming out properly on excel when I compare it to FermCalc. I'm using the formula's on Fermcalc's website:

mm = v(fcm - ci)mwm / (2mws)

 

[sour_grapes]

awesome feedback. I ran into an issue this morning putting in sulfite calculations. I'm fairly confident my formula is right, but it's not coming out properly on excel when I compare it to FermCalc. I'm using the formula's on Fermcalc's website:

mm = v(fcm - ci)mwm / (2mws)

Can you clue us in on what those variables are?

 

[mendozer]

mass of metabisulfite= volume in liters(f is the factor related to ph [10^pH-constant + 1]) molecular weight of metabisulfite (222.3 mols) / 2* molecular weight of sulfite (62/06 mols)

 

[RottenGrapeJuice]

Was this ever finished? Would love to use it. Save me some time of making one myself.

 

[mendozer]

yes but i can't upload excel workbooks here apparently. Send me a conversation with your email

 

[sour_grapes]

yes but i can't upload excel workbooks here apparently. Send me a conversation with your email

If you wanted to, you can compress ("zip") the file, and upload the zipped file.

 

[mendozer]

boom there we go

 

[mendozer]

I suppose you could put all of this on one sheet, and your actual Log on another sheet and link the cells so one has data adjustments and the other is for recording things.

 

[mendozer]

I didn't have the brix to SG and potential ABV active as a calculation so i updated it