Quantcast

Master Excel Winemaking Spreadsheet

Wine Making Talk

Help Support Wine Making Talk:

mendozer

Member
Joined
Sep 19, 2012
Messages
75
Reaction score
4
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

Senior Member
WMT Supporter
Joined
Sep 1, 2011
Messages
472
Reaction score
200
Location
Northern wisconsin
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

Victim of the Invasion of the Avatar Snatchers
Joined
Sep 19, 2013
Messages
11,656
Reaction score
10,043
Location
near Milwaukee
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

Senior Member
Joined
Jan 29, 2014
Messages
1,200
Reaction score
843
Location
Midwest
* 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

Member
Joined
Sep 19, 2012
Messages
75
Reaction score
4
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

Victim of the Invasion of the Avatar Snatchers
Joined
Sep 19, 2013
Messages
11,656
Reaction score
10,043
Location
near Milwaukee
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

Member
Joined
Sep 19, 2012
Messages
75
Reaction score
4
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)
 

Latest posts

Top