Water Chemistry Calculator pH Discrepancy

[ScrewyBrewer]

My original intent was that it would be hidden in and Excel Add-In (or whatever they are called). The user would not be exposed to the actual code behind Qcarb any more than he is exposed to the code for VLOOKUP.

I did create an Excel Addin in the format of a COM object. It's loaded the same way we reference and include the Solver Addin. The trouble is not everyone is skilled enough in Excel to know how to install and use the Addin. It's a shame because the Addin code is binary making it very efficient while also making it nearly impossible to crack. Unfortunately, the Addin will not run in anything other than a Windows environment.

 

[ajdelange]

Sorry guys, I'm just in this thread to clean up some nastiness, but I'd like to comment on the statements above. In the past, I've been in the embarrassing position of defending a mathematical solution to some real-world measurements, when ultimately the empirically-derived table lookup was just better. I now know that in the real world there are confounding variables that the math doesn't appreciate, but the measured data certainly does

If you are trying to say that empically derived results based on careful measurements are better than predictions based on a faulty math model then that is true. The key word here is "careful". That means that the measurements, on the one hand, must be accurate and on the other that they span the entire set of conditions over which we are interested.

The example that comes to mind is my attempts to model the absorption spectra of beer. I started, of course, by measuring the absorption spectra of a set of beers. Clearly, I had to make my absorption measurements using techniques that insured the numbers accurately represented the beers' actual absorptions. Assuming that I did that properly it was pretty clear that a good empirical model was a double exponential. Trying that against a lambic, however, there were appreciable errors. Why? Because the original ensemble of beers upon which the double exponential conclusion was drawn did not include any lambics. My measurements were not careful enough. So, add some lambics into the set and guess what? The double exponential doesn't look like such a good model any more. Push the error down in one place and it pops up somewhere else. This is the main problem with the empirical approach. Not to mention that inquiring minds want to know what is going on physically

Martin in his recent post appears to take the position that the empirical approach is superior because, while one may have an accurate mathematical model for some aspect of a chemical reaction there are other aspects that are not covered by it that the empirical approach would pick up. This can be the case. Consider Gallileo and his cannon balls. He might from empirical observation determine that the time it takes a cannon ball to fall a given distance is proportional to the 0.45th power of the height from which he dropped it. Considerations of conservation of energy would lead him to conclude that the time is proportional to the 0.5 power. If he dropped bocce balls instead of cannon balls he might draw another set of conclusions (different power) from empirical observation but still cling to the square root because gravity is a conservative field. In doing so he is ignoring drag and so the empirical data might indeed give better results than the "robust" mathematical model. But the model is not really robust. It ignores drag.

So I asked Martin to give us a hint as to which part of the reactions it is that he fells we have not modeled so that, if they are significant, we can have a look at them.

 

[ajdelange]

The base 10 Logarithm itself is the very basis of pH.

The thing that you consistently miss here is that you can use pH by itself to calculate the charge on water and only water with it's pK of 14. The pH scale is based on this pK. pH does not give the charge on malt or bicarbonate ion or chloride ion unless the pK's for those substances are included in the calculation. The fact that however you use it may sometimes give a reasonable answer is proabably as much coincidence as anything else. I can come up with an algorithm that predicts that, given the amount of snow on the ground here the weather in Alice Springs, NT is fine. It works but has no scientific or other basis.

 

[maisch]

Because I'm not skilled at such things. The whole intent of the project was to make the science accessible to those who are. I have prepared a spreadsheet using them mostly as a demo platform and find it extremely useful and easy to use relative to previous spreadsheets I have done but am well aware that someone who does this for a living could doubtless do it much better.

I can understand just not wanting to but to say you're not skilled and use that as an excuse might be a stretch.

Perhaps if the project communicated the algorithms, not sure if that's how chemistry works, but the algorithms would be programming language and environment independent.

Because everybody has got Excel. If you can't figure out how to port the meat functions to any other programming language you are a worse programmer than I. The exceptions here would be the functions that gather up the data from the spreadsheet i.e. get what the user enters to the bits that do the actual computation. That is, of course, going to depend on the UI and that in turn will depend on the environment.

Porting to another programming language is fairly easy but as you've mentioned you've tied it to the Excel UI.

It was never intended to be elegant - just functional. My original intent was that it would be hidden in and Excel Add-In (or whatever they are called). The user would not be exposed to the actual code behind Qcarb any more than he is exposed to the code for VLOOKUP.

Functional is good, dependent on Excel is bad.

Visual Basic is an OOP language.

The VB6 and VBScript on the back end of Excel are not considered OOP languages. They are procedural and event driven but not OOP. VB6 has a "class" construct but it's not well refined and compared to modern languages not considered full on OOP.

As is manifestly clear in this thread and others water chemistry is not something that is comprehensible to the average home brewer (nor to most commercial brewers either).

Can those well versed in chemistry easily follow and comprehend?

Given the last comment, one of the goals here was to make functions that would, for example, return the charge on a liter of water or the charge on a killogram of malt available to the user without explaining how they are computed or why. The job of pH estimation then becomes that of setting things up so that the net sum gain in charge over a mash is 0. Given that only perhaps two or three people here understand that it appears that this approach is not going to lead to success.

Again presenting a step by step algorithm and explanation vs functions with magic numbers might be a better approach.

Impossible. For some reason this particular area of chemistry is very hard to understand.

Simplification via step by step algorithm might help the situation.


My goal isn't to dis you but to encourage the release of Excel (or spreadsheet in general) independent functions and if possible algorithms that list step by step what is required. I'm sure we could go back and forth on the subject but that's not my interest either, I've spoken my piece.

 

[ajdelange]

I can understand just not wanting to but to say you're not skilled and use that as an excuse might be a stretch.

You have misinterpreted what I said. I am offering no excuses or apologies: just telling you why it is the way it is. If you think you can do it better (and you probably can) then do it.

Perhaps if the project communicated the algorithms, not sure if that's how chemistry works, but the algorithms would be programming language and environment independent.

The project communicates the "algorithms" quite clearly. The most important ones are simple evaluations of formulas which clearly discernable from the code. The second are implementations of well know root finders (bisection, Newton's method) with which any programmer should be familiar. The third are algorithms that gather up the data from the user interface which in this case happens to be based on Excel and which clearly need to be modified to work with whatever platform you choose to implement on.

Porting to another programming language is fairly easy but as you've mentioned you've tied it to the Excel UI.

Yes, it is trivial to port type I and type II functions described above. There is no way I can clarify for you what you need to do to gather input data from whatever platform you may decide to implement because I have no idea what choice you will make nor would I be able to even if I knew.

Functional is good, dependent on Excel is bad.

Nothing wrong with Excel for the current purpose. Not saying there might not be a better environment but if you choose one you'll have to modify user interfaced. If you can't port the type I and type II stuff I question as to whether you would be able to do that.

Can those well versed in chemistry easily follow and comprehend?

Anyone should be able to. The code makes it very clear what formulas are being evaluated. Everybody who took high school math knows Newton's method and any programmer should know root bisection.

Again presenting a step by step algorithm and explanation vs functions with magic numbers might be a better approach.

Simplification via step by step algorithm might help the situation.

Look at Qacid. Then look at the steps for computing Q in the Sticky at the top of the page. Do you see any similarities?

My goal isn't to dis you but to encourage the release of Excel (or spreadsheet in general) independent functions and if possible algorithms that list step by step what is required.

If you want Excel independent (whatever that means) functions you are going to have to write them yourself. I could, I suppose, rewrite all the functions in something like Pseudo-ALGOL but what would be the point? It wouldn't take any more effort to port from VB to any other language than it does from Pseudo ALGOL.

 

[maisch]

You have misinterpreted what I said. I am offering no excuses or apologies: just telling you why it is the way it is. If you think you can do it better (and you probably can) then do it.

I have no doubt that I can but so can you!

The project communicates the "algorithms" quite clearly. The most important ones are simple evaluations of formulas which clearly discernable from the code. The second are implementations of well know root finders (bisection, Newton's method) with which any programmer should be familiar. The third are algorithms that gather up the data from the user interface which in this case happens to be based on Excel and which clearly need to be modified to work with whatever platform you choose to implement on.

I'm not talking about data or numerical recipes algorithms, I'm talking about the chemistry algorithms to find the pH. Everything from molecular weight calculations to unit conversions to the charge calculation.

Yes, it is trivial to port type I and type II functions described above. There is no way I can clarify for you what you need to do to gather input data from whatever platform you may decide to implement because I have no idea what choice you will make nor would I be able to even if I knew.

There is a way, you can code functions and procedures that take language/platform independent inputs.

Nothing wrong with Excel for the current purpose. Not saying there might not be a better environment but if you choose one you'll have to modify user interfaced. If you can't port the type I and type II stuff I question as to whether you would be able to do that.

If you want Excel independent (whatever that means) functions you are going to have to write them yourself. I could, I suppose, rewrite all the functions in something like Pseudo-ALGOL but what would be the point? It wouldn't take any more effort to port from VB to any other language than it does from Pseudo ALGOL.

There are many cross platform environments like .NET, Java, etc...

 

[ScrewyBrewer]

For what it’s worth trying to replicate the Excel Solver functionality in Excel without relying on the Solver Addin is no trivial feat. Whether using VBA JavaScript or any other language.

 

[ajdelange]

I have no doubt that I can but so can you!

But why would I? These tools are there for you to do whatever you want with. Yes, in the demo spreadsheet I often "improve" things, add functionality etc. but these improvements make things more to my liking. I do use the tools in at least one other environment where I want to process data in ways where a spread sheet would be clumsy.

I'm not talking about data or numerical recipes algorithms, I'm talking about the chemistry algorithms to find the pH. Everything from molecular weight calculations to unit conversions to the charge calculation.

You are either not following me, not reading what I have written or have no concept of how scientific programming is done. I have mentioned several times that if you will simply look at the code for Qacid, which is probably the most basic tool in the drawer, you will, or should be able to, comprehend the 'algortithm' for computing charge on the anions of a polyprotic acid. If you cannot you can look at the sticky here or read the manual on the yola site. If after doing those things you are still unable to understand how Q is computed then I am afraid you are not going to be able to benefit from these tools. So be it. I can't play the violin. Others here have been able to use them with some success. Your talents may lie in other areas of endeavour.

There is a way, you can code functions and procedures that take language/platform independent inputs.

Yes, yes, I know that and I keep repeating that I know that and you keep stating that over and over again. A child can look at the Excel Qacid function or the IGOR Qacid function and recast it in any form he wants because it is glaringly obvious from it what one must do to compute Q from pH and the pK's.

I will answer any questions about the details of any of the algorithms contained but I am not willing to restate the same set of facts over and over again because if you haven't gotten it at this point you aren't going to get it. Look at some of the code. Read the manual. Check the stickies.

 

[ajdelange]

For what it’s worth trying to replicate the Excel Solver functionality in Excel without relying on the Solver Addin is no trivial feat. Whether using VBA JavaScript or any other language.

Given the universe of things that Solver will do and the conditions under which it will do them then this statement is true. But surely you cannot mean this in the context of finding the pH of a mash. Because of the near linearity and the limited range of possible answers one can use trivial methods like root bisection or Newtons's method. The only part that is annoying is the part in which you must gather the information about the amount of each malt, the alkalinity of the water, the source pH, the amount of acids/bases added from wherever you have had your user put them. That can be tedious but it's hardly difficult. If that's what you mean by non trivial then I agree but once you have a function that returns ∆Q finding its root (what Solver does) is indeed trivial.

 

[ScrewyBrewer]

Given the universe of things that Solver will do and the conditions under which it will do them then this statement is true. But surely you cannot mean this in the context of finding the pH of a mash. Because of the near linearity and the limited range of possible answers one can use trivial methods like root bisection or Newtons's method. The only part that is annoying is the part in which you must gather the information about the amount of each malt, the alkalinity of the water, the source pH, the amount of acids/bases added from wherever you have had your user put them. That can be tedious but it's hardly difficult. If that's what you mean by non trivial then I agree but once you have a function that returns ∆Q finding its root (what Solver does) is indeed trivial.

Yes, that is what I mean AJ. If Solver is a key component of your voltmeter and I think it is then trying to code its functionality is well beyond myself and most likely any other developer frequenting this forum.

 

[ajdelange]

I'm a bit floored by this. VBA has a function (Cells) to which you pass a range and an offset from the origin and it returns the value in the cell offset from the origin by the offset you pass. This allows you to organize your input data (alkalinity, water volume, mass of each malt, malt identifier...) in a column and your malt and acids data into tables and access them very simply within VBA. Thus for a malt you would use pass the origin of the malts table to cells along with the row (for the particular malt) and column (for the various pieces of malt data i.e. pHDi, a1, a2 and a3) and cells.value will return the individual data items for that malt from which ∆Q for that malt is trivially calculated for a given pH. Are you really saying that this is beyond the capability of the average developer. This has got to be introductory stuff fort the student of VBA (or any programming language). The only thing that is burdensome is that you must have code for each possible type (you can loop over the malt entries) of material which can be added to a mash so that a lot of lines of code may be needed to compute Q for a mash.

Is this at odds with your experiences?

 

[ScrewyBrewer]

Is this at odds with your experiences?

No, I was talking about replacing the Solver Addin functionality. Are you saying your voltmeter works without using the Solver Addin?

 

[Big Monk]

No, I was talking about replacing the Solver Addin functionality. Are you saying your voltmeter works without using the Solver Addin?

A.J.'s most recent stuff does not use Solver, IIRC.

That's actually part of my problem. I have not had the the time to adapt that stuff and eliminate the solver in my own sheet yet.

 

[ajdelange]

No, I was talking about replacing the Solver Addin functionality. Are you saying your voltmeter works without using the Solver Addin?

Yes. Got rid of the Solver way last summer.

 

[Big Monk]

Yes. Got rid of the Solver way last summer.

My sheet still uses the solver and preserves the functional basis of your new sheet.

I just haven’t had the time to adapt your newer stuff. The solver works well for me but many have had trouble getting to work with thier versions of Excel.

I’m dissecting your current version and playing around.

 

[Silver_Is_Money]

Yes indeed they can when the full understanding and/or application of the chemistry is not incorporated.

In other words, if you've got a great and factual calculation for a part of the chemical reaction and haven't accounted for other aspects of that reaction, then you are no better off than an empirical estimate.

I fully agree with this from the perspective that if the entirety of the chemistry of a system is not understood and thereby incorporated into the math models, empirical estimates may thereby be more accurate. I.E., if something within the reality of what is taking place at the micro-chemical level for all of the various beer reactions and interactions is unknown, or not completely and thereby correctly understood, then the 100% chemistry approach is subject to error (subject to being incorrect) and an empirical model may for that specifically defined case come closer to mirroring or perhaps more accurately mimicking "measured" observation.

The question to be asked is then: Do we at this juncture fully understand and can we thereby fully quantify the chemistry of all of the intricate reactions and interactions potentially taking place during the mash?

 

[Big Monk]

The question to be asked is then: Do we at this juncture fully understand and can we thereby fully quantify the chemistry of all of the intricate reactions and interactions potentially taking place during the mash?

The charge conservation model handles the full understanding of mash chemistry.

In my opinion the only thing holding it back is the quantity of malt titration data and trying to establish a base set of malt classes and corresponding titration and DI pH data for use in a spreadsheet. I have a decent list of malt classes together but I want to review the data again and reclass them all.

When you think about it though, do you really know the color of your malt well enough to trust inputting it into a spreadsheet? I typically do because I use Weyermann for everything and my LHBS is good about saving the bar codes from the malt sacks. So I can trace that malt to it's sheet for the exact measured color. The average homebrewer only uses the range on the bag and who knows if that's accurate or the LHBS pulled it off their suppliers website prior to repacking it by pound.

 

[Silver_Is_Money]

I can state here that even General and Special Relativity are incomplete math models of reality since they fail at the quantum level, and quantum models are incomplete since they fail at the macro level, and particularly at the level of modeling gravity. And as such, both approaches might even be perceived to be merely elaborate empirical math models bent upon mirroring or mimicking reality. At the philosophical level, one might even ask if reality itself is math or something other than math. So to presume that all of beer science is fully understood may be stretching things a bit.

 

[Big Monk]

I can state here that even General and Special Relativity are incomplete math models of reality since they fail at the quantum level, and quantum models are incomplete since they fail at the macro level, and particularly at the level of modeling gravity. And as such, both approaches might even be perceived to be merely elaborate empirical math models. At the philosophical level, one might even ask if reality itself is math or something other than math. So to presume that all of beer science is fully understood may be stretching things a bit.

I didn't say beer science is fully understood. I said that to the level that we can understand mash chemistry, we do. Also, it ain't quantum physics. We should stray from trying to make it more complicated than it is.

Homebrewing is a pretty interesting hobby in that we tend to try and make the easy stuff hard and the hard stuff easy.

Anyone listening to what A.J. has been saying for years, and understanding the model presented in his troubleshooter in August, will understand that the chemistry is pretty straightforward.

Handing it accurate titration data and an accurate source water (if using one) are the hiccups, but those hiccups exist with empirical models as well, i.e. do you actually know the color of your grain and the makeup of your source water?

Add to that the fact that even if you have a great model, you still have to actually MEASURE the pH, and through discussion all over the forums, this isn't a guaranteed success from brewer to brewer.

So I am pursuing a certain track because as a model, the charge conservation method introduces the least mathematical error if fed good data. I know that at the very least I can measure pH within the error of the meter and the buffers. The goal is that all of this just becomes commonplace for the average brewer in the years to come.

Then you have to factor in though the idea that some people just don't care about measuring or estimating pH at all, and we start to look like a bunch of hot air filled windbags!

 

[Silver_Is_Money]

Since it appears that just about any model can predict that a brew consisting of essentially all base malt will need acidity, the math model battlefield must be shifted to the realm of high quantities of crystal (caramel) and deep roasted malts. If repeatedly measured pH's for this type of beers during the mash are below modeled predictions, then the validity of the science behind the model itself must be questioned. It appears to me that many to most real world measurements of the likes of Stouts and Porters during the mash have been reported on this forum to approach or even for some cases dip very slightly below pH 5.0, while the majority of the math models are seemingly stuck at predictions of 5.3 to 5.5, such that that one begins to question and wonder. I'll admit that most pH measurements are mistakenly taken at only 10 to 15 minutes into the mash, and have an artificial degree of measured pH lowness imposed upon them thereby. And that there is a strong likelihood of samples not being fully cooled, or of samples being stirred during measurement, both of which technique errors also bring with them their own false low pH readings. And lastly, pH meters require far more time to stabilize a reading than the stability indicator light on the meter allows for, so most do not let the meter sit in the sample for a few minutes, but rather take a hasty and thereby false low pH reading. If error is involved in timing or technique or temperature, a false low pH reading is inevitably going to be the consequence. So measurement errors such as these all need to be weeded out.

 

[Big Monk]

Since it appears that just about any model can predict that a brew consisting of essentially all base malt will need acidity, the math model battlefield must be shifted to the realm of high quantities of crystal (caramel) and deep roasted malts. If repeatedly measured pH's for this type of beers during the mash are below modeled predictions, then the validity of the science behind the model itself must be questioned. It appears to me that many to most real world measurements of the likes of Stouts and Porters during the mash have been reported on this forum to approach or even for some cases dip very slightly below pH 5.0, while the majority of the math models are seemingly stuck at predictions of 5.3 to 5.5, such that that one begins to question and wonder. I'll admit that most pH measurements are mistakenly taken at only 10 to 15 minutes into the mash, and have an artificial degree of measured pH lowness imposed upon them thereby. And that there is a strong likelihood of samples not being fully cooled, or of samples being stirred during measurement, both of which technique errors also bring with them their own false low pH readings. If error is involved in timing or technique or temperature, a false low pH reading is the consequence. So errors such as this need to be weeded out.

The main differences I see right now are how the sheets deal with mineralization and how they handle Crystal and Roast malts.

How that plays out in the real world depends on who is testing. I'd like to think that if a pH measurement procedure followed by all testers was implemented, that this could be crowd sourced.

 

[ajdelange]

I fully agree with this from the perspective that if the entirety of the chemistry of a system is not understood and thereby incorporated into the math models, empirical estimates may thereby be more accurate. I.E., if something within the reality of what is taking place at the micro-chemical level for all of the various beer reactions and interactions is unknown, or not completely and thereby correctly understood, then the 100% chemistry approach is subject to error (subject to being incorrect) and an empirical model may for that specifically defined case come closer to mirroring or perhaps more accurately mimicking "measured" observation.

The chemistry is the same for lactic acid and mevalonic acid. There are no aspects of the dissociation of either that we don't understand. We do, for example, understand that the way they dissociate depends on ionic strength so if we are being really nerdy we write a spreadsheet that calculates ionic strength and its effects. We have a radio button that allows us to consider ionic strength or not. When we push it it doesn't make a noticeable difference in the results. So we conclude that we didn't have to go through all the pain of computing ionic strength and can ignore it. Ignoring it is equivalent to not understanding that it is part of acid/base chemistry but in our application it does not matter so we can be ignorant or lazy with respect to it.

The question to be asked is then: Do we at this juncture fully understand and can we thereby fully quantify the chemistry of all of the intricate reactions and interactions potentially taking place during the mash?

As I indicated above the mechanism of reaction is well understood and is the same for all acids and bases. The reaction for mevalonic acid is not different from that of lactic acid. By not considering lactic acid we are not falling victim to ignorance of the chemistry. If we are guilty of anything it is laziness. We don't want to be bothered with tallying up all the 'microreactions' and justify our laziness because we know that mevalonic acid is not a player in brewing water chemistry. Now were it. And let's say that it actually is. It would get picked up in the malt measurements just as the other acids are.



The question I keep asking here is "What aspect of the chemistry do you (or does Martin) think that we may be missing?" I haven't gotten an answer to that yet. One example might be ionic strength. I can put that into the Functions but I haven't done it because its effect doesn't amount to a hill of beans. I do think about it because it requires iteration and handling iteration in a regular spreadsheet requires a lot of real estate while a VBA function approach is much neater. The big issue is bookkeeping.

Another example of ignorance with respect to chemistry would be to put a mash thickness term into the equation for pH estimation which apparently Martin has done guided by Kai who saw the error and fixed in his programs.

 

[Silver_Is_Money]

Another example of ignorance with respect to chemistry would be to put a mash thickness term into the equation for pH estimation which apparently Martin has done guided by Kai who saw the error and fixed in his programs.

Although Mash Made Easy does not (presently at least) consider dilution (as for water to grist ratio) to impact mash pH (aside from some extremely small and close to unnoticeable vestige of empiricism from past versions which is still present, and which I need to weed out of my code with respect to water to grist ratio), I have began to think of the potential pH altering ramifications of this dilution (or if in the opposite direction, concentration) phenomenon. In keeping with log base 10, and when considering that pH is correlated to the molar concentration of H+, I offer this as a simplistic example of what I've been pondering (be it correct or incorrect, I simply don't know at this juncture).

If we begin with a grist that in 5 gallons of mash water yields a pH of 5.0, then:

10^-5.0 = 0.00001, whereby 0.00001 can be seen as the molar concentration of H+ species present within the wort.

Now if we dilute to 10 gallons (or effectively, if we mash in 10 gallons):

0.00001 molar H+ / 2 = 0.000005 molar H+

Converting this molar concentration of H+ back to a pH:

-log(0.000005) = 5.30103 pH

Thus in going from a mash at 5 gallons to a mash at 10 gallons (for this example) an upward pH shift of 0.3 pH points is witnessed.

I believe that something along these lines (if not factually precisely along these lines) of reasoning is taking place within BW. This is why in BW the mash pH goes low for the case of 5 gallons of mash water vs. 10 gallons, and extremely low, for the case of 3.9 gallons of mash water vs. 10 gallons, etc...

PS: I realize that within the above simplified model I have not factored in the H+ and/or OH- contributions of the water itself.

 

[ajdelange]

If you have 5 gallons of DI water to which you have added .01 mmol HCl/L the pH will be 5 and if you double the water volume it will go up to 5.3.

If you have added equimolar amounts of a monoprotic acid with a pK 5.0 and of one of that acid's salts you will also have a solution at pH 5. If you double the water in that solution (without addition of more acid and salt) the pH will NOT change by 0.3 but by a much smaller amount. This is a buffered system. Buffering resists changes in pH when external acids or bases are added. Water is a base in this case but an extremely weak one. You have to divest yourself of this notion which you clearly hold that protons are like fish in a pool so that if you increase the volume of water there are fewer fish per unit volume. When you increase the volume of water in a buffering system the acid side of the system releases more protons to protonate the new water molecules. The reason this does not happen with hydrochloric acid is because all the protons are already released. In a buffered system, and mash is one, they aren't.

As to what happens in Brun water, dmr recently posted a very plausible explanation as to what the problem is and where it came from.