A thought experiment to test the general validity of available mash pH software

[ScrewyBrewer]

....Of course these things require everyone to put aside their pride ("my" spreadsheet, "my" algorithm) and place effort and energy into the construction of the library.

And your contribution to this project would be...?

 

[passedpawn]

And your contribution to this project would be...?

He discussed on topic. That is enough.

 

[ajdelange]

But does it still use 'Solver'?

It does not use the Solver to determine what the mash pH estimate is or how much lactic acid is needed to hit pH 5.4 with a particular grain bill and water parameters. But it still can be used if, for example you want to hit a particular pH in a grain bill in which crystal malt is 10 percent of the total and sauermalz no more than 3%.

 

[ajdelange]

AJ is programming VBA Functions.

That is correct.

It does not use solver.

It does not use Solver to solve the unconstrained problems of finding mash pH and the amounts of acid or base to add to realize a given pH. As such it will blithely estimate a mash pH of -7 if the math drives it that way. If you try to add acid to a recipe that is already acidic it won't let you and will admonish you to add base instead. Nut please remember that I am, at this point, only playing around with new toys. The Solver can and should be used to Solve, for example, constrained problems (e.g. want no more than x% sauermalz).

The only question I have is if the inclusion of VBA code into Excel makes it less compatible with a wide array of computing platforms.

Another question with VBA is security. At one time Microsoft removed it from Excel because of security concerns. One can still save spreadsheets in formats that will not run macros.

 

[ajdelange]

Newton-Raphson is an algorithm that can be used in place of solver.

It can be used in place of the Solver for certain types of problems like the one of finding mash pH. Yes, Q is a non linear function of pH but it is a monotonic one so that there is only a single root. This is ideal for Newton. Other classes of problems it cannot do and so is not a substitute for Solver. Solver can handle constrained problems and multidimensional ones. Newton can handle multidimensional ones too (e.g. finding the set of salt additions that best match and ion profile) but I can't see doing those in Excel (other than with Solver) because it cannot, AFAIK, invert a non square matrix.

The book "Numerical Recipes in C" contains implementations useful in the context given in this thread. (or Fortran)

Numerical Recipes in FORTRAN is probably the most useful book I ever owned but one hardly needs anything in it for this application. Sure it's got Newton Raphson and Root Bisection but these are trivial algorithms compared to much of what's in that book. For example the code for finding mash pH is

Function FindpHz(pH0 As Double) As Double
'Find pH that 0's pHz starting from an initial guess at pH0

Dim dQdpH As Double
Dim QpH0 As Double
Dim Corr As Double
Corr = 0.1

Do Until Abs(Corr) < 0.0001
QpH0 = CalcQ(pH0)
dQdpH = (CalcQ(pH0 + 1e-07) - QpH0) / 1e-07
Corr = -QpH0 / dQdpH
pH0 = pH0 + Corr
Loop

FindpHz = pH0

End Function

That's it!

Of course we could take the SVD code from Recipes and translate it into VBA thus allowing us to invert matrices...

It would be best if a proper software library was coded (with classes, types, structures, functions etc...) to represent the chemical reactions taking place (proton surfeit/defecit) and the water calculations (dilution, combination, etc).

The chemistry here is really simple. Proton deficits and surfeits for any acid, weak or strong, mono or polyprotic, can be had from

Function QAcid(pH As Double, pK1 As Double, Optional pK2 As Double = 60, Optional pK3 As Double = 60, Optional pK4 As Double = 60) As Double
Dim r1 As Double
Dim r2 As Double
Dim r3 As Double
Dim r4 As Double
Dim f0 As Double
Dim f1 As Double
Dim f2 As Double
Dim f3 As Double
Dim f4 As Double

r1 = 10 ^ (pH - pK1)
r2 = 10 ^ (pH - pK2)
r3 = 10 ^ (pH - pK3)
r4 = 10 ^ (pH - pK4)

f0 = 1 / (1 + r1 + r1 * r2 + r1 * r2 * r3 + r1 * r2 * r3 * r4)
f1 = f0 * r1
f2 = f1 * r2
f3 = f2 * r3
f4 = f3 * r4

QAcid = -(f1 + 2 * f2 + 3 * f3 + 4 * f4)
End Function

Another, I think you'll agree, pretty simple bit of code. The challenge and where the heavy lifting has to be done is in organizing this handful of simple functions into classes (or whatever the jargon of OOP is) and merged with objects that provide a simple, understandable and friendly user interface.

It seems Mr. Delange has a spreadsheet on his website and several posts with these algorithms on this forum and his website which could be used to implement such software.

It's not on my website. There is one there that is so primitive I ginned it up while waiting for my turn to speak at an MBAA meeting. But you can use it to get answers using the Solver. This stuff I'm posting about today was cooked up today so it isn't exactly time tested.

 

[ajdelange]

He discussed on topic. That is enough.

As he 'liked' the post he quoted I don't think he was being snarky here. Just asking what part the poster might be willing to take in this endeavor.

 

[sixhotdogneck]

I guess I could contribute to a spec and of course coding but off the top of my head:

Base Classes/Interfaces: Salt, Acid, Grain, Water, Mash

Salt:
Properties: Molecular weight, Salt Name, Proton Contribution
Functions:
Operators:

Acid:
Properties: pH, pK1, pK2, pK3, pK4, Acid Name, normality
Functions: DensityPH - determine acid density at specified pH probably from a formula derived from online data (20C or 25C)
Operators:

Grain:
Properties: pHDI, a, b, c, Grain Type
Functions:
Operators:

Water:
Properties: pH, Ca PPM, Mg PPM, Na PPM, Cl PPM, SO4 PPM, HCO3 PPM
Functions:
Acidify - would determine how much (weight/volume) of a specific acid would be needed to bring this waters pH to a target pH and modify the water as such (Adding the acid flavor ions)
AddSalt - would add a specified amount of specific salt to this volume of water
Operators:
'+' would combine two volumes of water to produce a new water
'+' would add a specified amount of salt to this water

Mash:
Properties: List of Temperature Steps, List of Strike Waters, List of Sparge Waters, List of Grains, Target pH
Functions:
AddWater - would add a water
AddGrain - would add a grain
MashPH - would calculate the pH of the mash given the list of strike waters, list of grains and output a list of acids and a list of salts to add
Operators:

Derived Classes:

Salts: Calcium, Magnesium, Sodium, Chloride, Sulfate, Bicarbonate - add property "weight"
Acid: Lactic, Hydrochloric, Sulfuric, Phosphoric, Citric, Tartaric, Malic - add property "volume"
Grain: add property "weight", add property "name"
Water: add property "volume"
Mash: add property ""

Error checking would be slim and fall on the consumer of the library - IOW user would be allowed to create unrealistic mash pH's and waters

Usage would be similar to that in my post above. Usability is always determined by using the library.

This is all just back of the napkin but perhaps a step in the right direction.

 

[ScrewyBrewer]

He discussed on topic. That is enough.

Maybe for you it is but I'm interested enough to find out more.

 

[ScrewyBrewer]

@sixhotdogneck @ajdelange I have advanced skills in Excel VBA and Visual Basic 6.0. Porting Excel VBA code to a distibuted .Dll file containing the different classes is another possible approach. The downside to the approach is limiting its use to Microsoft products, if the user's don't already to subscribe to them.

 

[SteveH aka shetc]

I'll set up the sprint planning

 

[passedpawn]

Maybe for you it is but I'm interested enough to find out more.

Sorry, I complete misinterpreted the meaning of your post above. I'll butt out now...

 

[Silver_Is_Money]

There may be operating system hope for those of us who are completely Microsoft intolerant. A team in Russia (presumably where Microsoft can't touch them) is presently working on an operating system called 'ReactOS', which intends to be an NT level compliant and also fully clean code 'clone' of the Microsoft operating system. It will be free. It is currently in the Alpha stage, and both "full install" and "run me from a CD or DVD" versions are available now. If it runs Excel (and if it doesn't at present, it will somewhere down the road a bit), the only remaining issue would be purchasing Excel. Having to purchase Excel while being Microsoft intolerant remains a problem though.

 

[schematix]

I don’t mind the windows and excel combination, but then again I have 3 windows laptops that all have excel so I might be biased.

Maybe this could be something developed platform agnostic.... say html5 and java script ?

 

[Big Monk]

I don’t mind the windows and excel combination, but then again I have 3 windows laptops that all have excel so I might be biased.

Maybe this could be something developed platform agnostic.... say html5 and java script ?

The question is: on who's time and dime? Excel is convenient for me because:

a.) I have it
b.) I can work quickly in it so it doesn't take me a ton of time

You start talking about TRUE coding, doing web-based stuff, etc. and:

a.) My eyes glaze over
b.) It would take up too much of my time, personally

 

[schematix]

The question is: on who's time and dime? Excel is convenient for me because:

a.) I have it
b.) I can work quickly in it so it doesn't take me a ton of time

You start talking about TRUE coding, doing web-based stuff, etc. and:

a.) My eyes glaze over
b.) It would take up too much of my time, personally

A lot of people can write code. Not as many understand this specific chemistry. I'm sure more than one programmer has the aptitude to understand the math and chemistry presented and run with it.

If AJ were hit by a bus we'd all be screwed though.

 

[ScrewyBrewer]

That is correct.

@ajdelange VBA code compiled into a Visual Studio 6.0 .Dll file can be called from worksheet cells saved as an .xlsx format. No macro or VBA code required by the worksheet.

When I try to compile your functions I get an 'Sub or Function Not Defined' error on the CalcQ(pH0) call.

Function FindpHz(pH0 As Double) As Double
'Find pH that 0's pHz starting from an initial guess at pH0
Dim dQdpH As Double
Dim QpH0 As Double
Dim Corr As Double
Corr = 0.1
Do Until Abs(Corr) < 0.0001
QpH0 = CalcQ(pH0)
dQdpH = (CalcQ(pH0 + 0.0000001) - QpH0) / 0.0000001
Corr = -QpH0 / dQdpH
pH0 = pH0 + Corr
Loop
FindpHz = pH0
End Function

 

[ScrewyBrewer]

I don’t mind the windows and excel combination, but then again I have 3 windows laptops that all have excel so I might be biased.

Maybe this could be something developed platform agnostic.... say html5 and java script ?

I have experience implementing just what you describe. As long as the spreadsheet doesn't use VBA or macros.

Click here for an example.

 

[ajdelange]

When I try to compile your functions I get an 'Sub or Function Not Defined' error on the CalcQ(pH0) call.

Do you have the source for that? I don't recall sending it to you. In any case here it is:

Function CalcQ(pH As Double) As Double

Dim Gal As Double
Dim Alkppm As Double
Dim pHs As Double
Dim pHe As Double
Dim Liters As Double
Dim alkmEq As Double
Dim Row As Double

Gal = Range("B2").Value
Alkppm = Range("B3").Value
pHs = Range("B4").Value
pHe = Range("B5").Value
Liters = 3.785 * Gal
alkmEq = Alkppm / 50

CalcQ = Liters * (Qw1L(pH) - Qw1L(pHs) + Ct(alkmEq, pHs, pHe) * (QAcid(pH, 6.38, 10.38) - QAcid(pHs, 6.38, 10.38)))
CalcQ = CalcQ - Liters * (Range("B6").Value / 50 / Range("B11").Value + Range("B7").Value / 100 / Range("B11").Value)

For Malt = 0 To 4
If Cells(6 * Malt + 22, 2).Value = " " Then
Row = 0
Else
Row = Cells(Malt * 6 + 22, 2).Value + 3
CalcQ = CalcQ + Cells(Malt * 6 + 24, 2).Value * dQm1kg(pH, Cells(Row, 9).Value, Cells(Row, 10).Value, Cells(Row, 11).Value, Cells(Row, 12).Value)
End If
Next Malt

CalcQ = CalcQ - Range("B55").Value * LacNorm(pH, Range("B56").Value)
CalcQ = CalcQ - Range("B60").Value * PhosNorm(pH, Range("B61").Value)
CalcQ = CalcQ + Range("B65").Value
CalcQ = CalcQ + (1000 * Range("B67").Value / 84) * QBicarb(pH)
CalcQ = CalcQ + Range("B74").Value
End Function

This calls several other functions which I don't think I've sent to anyone but Scotty. Also it references particular cells in the particular spreadsheet I've put together.

So I think the point is that anyone who wants to experiment with this should get a copy of it from me. I think it's far enough along that I'd be willing to send it to anyone who wants to try it. It's heavily commented and so I think people ought to be able to figure out how to use it. So anyone who wants it PM me with an e-mail address and I'll send it to you.

The only caveat is that Users MUST understand the basic principle on which it works. That is, in a nutshell. that mash components (water, bicarbonate ions, acid molecules, the individual malts) all give up or grab protons in accordance with their properties and the pH of the environment in which they are placed. The pH a mash will come to is the pH that causes all the protons donated to be exactly equal to the number of protons grabbed. To get a mash component to a particular pH protons must either be supplied to it (in which case we say there is a proton deficit with respect to that pH) or absorbed from it (in which case we speak of a proton surfeit which we more conveniently express as a negative deficit). The spreadsheet simply calculates and lists the deficit for each component and sums the deficits. The mash pH is the pH that makes the sum 0. The spreadsheet finds that pH by an iterative technique (Newton's method).

 

[ScrewyBrewer]

Thank you AJ, private message sent with my email address. When adding the code from your current post the compiler is now balking about the (Qw1L(pH) function not found. Once I see the code in your spreadsheet everything will fall into place.

 

[ajdelange]

I'm really surprised at where this thing has wound up in a little over a week. It's incredibly powerful in what it can do. For example, change the alkalinity of the source water or the end point titration pH or the amount of a malt etc. and the estimated mash pH updates automatically. You can select up to 8 malts and use ones in a 'database' (on a separate sheet) or enter pHDI and buffering numbers for 'custom' malts. If you don't have the latter just use -40 and you'll get a pretty good estimate. Malt selection is by pull-down menu. It will calculate the amount of lactic acid, phosphoric acid, sodium bicarbonate or a malt (usually sauermalz) it takes to hit a desired mash pH at the push of a button (and warn you if you are trying to acidify an already to acidic mash). It uses none of the approximations the first generation spreadsheets use. For example it does not estimate mash pH based on the assumption that proton deficit is a liner function of pH but instead uses iterative techniques hidden in macros. One of the 'bells and whistles' it features is a plot of proton deficit vs pH. You can see in the picture how non linear the curve is. It takes into account the fact that the strength of phosphoric and lactic acid and the proton absorbing capacity of sodium bicarbonate depend on pH.

I think this is ready for the general user to play with. It's heavily commented so you should be able to figure out how to use it pretty quickly. Are there pronblems with it? There probably are. It's only a few hours old at this posting. But if anyone wants to give it a shot just drop me a PM with and e-mail address.

 

[ScrewyBrewer]

I think this is ready for the general user to play with. It's heavily commented so you should be able to figure out how to use it pretty quickly. Are there problems with it? There probably are. It's only a few hours old at this posting. But if anyone wants to give it a shot just drop me a PM with and e-mail address.

Thank you @ajdelange for this amazing and much needed work. I can't wait to put it to use!

 

[ajdelange]

Keep in mind that it started out as a teaching/diagnostic tool and it can still teach you a lot though in its current form all the heavy lifting is hidden in the macros (which, to my surprise, only total 365 lines of code, including white space).

I should again emphasize that there are two problems with spreadsheets (and calculators)
1)They model malts based on type, color or linear measurements. The models aren't very good
2)Even were they given excellent models they cut so many corners that computation errors are induced

This spreadsheet solves No. 2 but not No. 1. You can feed it a malt model just based on pHDI and assume the buffering is linear with a = - 40 and get pretty good results as long as pHDI is close to the target mash pH for each malt (the case with light beers). Where darker malts are involved you will get better results if you give the actual slope (a1) and include values for b and c (see recent Sticky on how to get those - it's a lot of work).

You can explore the consequences of truncated malt models easily. Just copy some full malt data to one of the arbitrary malt entries in the database, 0 b and c, set a to -40 and look at the predicted pH change as you switch from the actual malt data to the linearized data.

 

[ScrewyBrewer]

Keep in mind that it started out as a teaching/diagnostic tool and it can still teach you a lot though in its current form all the heavy lifting is hidden in the macros (which, to my surprise, only total 365 lines of code, including white space).

@ajdelange you and I share a passion for brewing water chemistry and now visual basic coding too. I corrected a small typo in the lactic acid code to eliminate a compile error. I look forward to spending most of my day getting acquainted with your voltmeter.

 

[Big Monk]

@ajdelange you and I share a passion for brewing water chemistry and now visual basic coding too. I corrected a small typo in the lactic acid code to eliminate a compile error. I look forward to spending most of my day getting acquainted with your voltmeter.

For cracking the nut on the concepts, I would use A.J.’s original solver based troubleshooter/voltmeter.

For implementing into a program or spreadsheet, this new sheet is the one to use.

They do the same thing but the science behind what’s going on is much more explicit in the original.

 

[ScrewyBrewer]

For cracking the nut on the concepts, I would use A.J.’s original solver based troubleshooter/voltmeter.

For implementing into a program or spreadsheet, this new sheet is the one to use.

They do the same thing but the science behind what’s going on is much more explicit in the original.

@RPIScotty agreed. There is a lot going on here and much to learn. Understanding the concepts of all is where I'm at now.

 

[Big Monk]

@RPIScotty agreed. There is a lot going on here and much to learn. Understanding the concepts of all is where I'm at now.

I can’t claim to k ow the science very well, although A.J. Is being more than generous with his time in private conversations in helping me to “get it”.

Mechanically speaking I know what does what in the proton deficit/surfeit model with a fair amount of confidence.

 

[pricelessbrewing]

I'll have to read through this a few times, but I will volunteer to help implement a web based version if there is interest. I've taken a few courses in chemistry, but my degrees in math and physics research, not chemistry.

Can start a git repo for this so it's free, open source, and available for multiple contributors, preferably released under something like the MIT license with appropriate credits to AJ and company as well as any developers that assist in creation.

@RPIScotty the link to version 1.1 is down for me.

 

[ajdelange]

Well this thing has turned into a tar baby. I think the real potential to the community would be in providing a package of brewing related VBA functions which the user could then use to put together spreadsheets of his own just as he can with the stock version of Excel put together spreadsheets using the included finance functions, trig functions, statistics functions etc. Just as the STDEV(number1,number2,...) function in your copy of Excel returns the standard deviation of the numbers passes as arguments so QAcid(pH, pK1, pK2...) would, with the proposed package installed, return the charge on 1 mole of acid with pK's pK1, pK2 etc. This makes it possible to generate very compact powerful spreadsheets but the problem is that no one seems to understand what the significance of the charge on 1 mole of an acid is with respect to the problems of interest. Thus making such a function available to brewers at large would not seem to be of much value to them and it would probably be better to proceed as has been done with the first generation spreadsheets: incorporate the macros in a conventional spreadsheet with them hidden from the user.

As to the platform: Excel certainly has its limitations as I have been finding out. Certain things seem to work some of the time and not others, some formats do not support macros at all, some functions do not do things in some formats that they do in another and operating some macros that change some cells will destroy the calculations in unrelated cells. I am sure that some of this is due to my naivete about Excel. Nevertheless it seems an Excel implementation would be accessible to more brewers than any other except perhaps something that ran on the web.

I've been busy in the last couple of days doing a water sheet to go with the malt sheet. It was, for the most part, pretty easy to put together and it is also pretty powerful in that it lets you blend two waters and add salts, bases or acids to come up with a blend you like with the minerals you like. I've been hung up for a couple of days on trying to send the results from the water page to the malt page. Sheet1.Range("A1").Value = Sheet2.Range("B7").Value does not work, for example.

For those of you who have requested a copy but haven't gotten it yet - standby. As soon as I have something I think is stable I'll get it out to you.

 

[ajdelange]

Late breaking news: Getting rid of my antiquated version of Excel and replacing it with 2016 (it turned out I had 2 more licenses) makes Sheet1.Range("A1").Value = Sheet2.Range("B7").Value work and I haven't had the problem with first execution after compile crash the thing. So stand by.

 

[ScrewyBrewer]

Late breaking news: Getting rid of my antiquated version of Excel and replacing it with 2016 (it turned out I had 2 more licenses) makes Sheet1.Range("A1").Value = Sheet2.Range("B7").Value work and I haven't had the problem with first execution after compile crash the thing. So stand by.

@ajdelange I'm in the process of compiling my cell formulas and VBA code into a .Dll library. The library is then referenced as an Excel Add-in and can be called from any version of Excel. The compiled native Windows byte code is very fast and its functions can be entered into cells or called from VBA. You may find this approach interesting.