The Water Engine

[Big Monk]

No problem! I guess I have a silly question. Where did the information come from to populate the malt class database?

Well, there are approximately 101 known (in this case published) sets of DI pH and titration data, given to me by A.J. deLange and culled from Riffe's 3rd installment of his pH adventures.

These malts were measured by deLange, Troester (these malts form the basis of the color acidity proxies that drive most sheets), Walts, Geurts, Bies, and Riffe.

The malt model is as follows:

dQMalt (mEq/kg) = a1 * (pHz - pH DI) + a2 * (pHz - pH DI) ^ 2 + a3 * (pHz - pH DI) ^ 3

QMalt (mEq) = kg * dQmalt

That is how you model a malt's acid/base characteristics in terms of its charge. There is no color component here.

A large majority of these measured malts are linear (single term) around pH DI and a1. Many, however are polynomial (2 and 3 term) around pH DI, a1, and a2 and pH DI, a1, a2, and a3 respectively.

What I did was list all these terms in a spreadsheet and organize them based on malt type. I then tried my hardest to classify them by type while staying true to the values that were measured, while extrapolating extra terms to make some of the malts polynomial based if I could.

It ain't perfect, but any of the malts where it seemed as though extrapolating or infering too excessively took them too far away from thier original measurements, were kept. However, if I had enough data to maybe give a Roasted Malt an a2 or a3 term, I did that.

Ideally though, you'd measure all these values for each malt you planed on using, typically when you purchased new bulk grain. So my method is admittedly a compromise, although one that is headed in a better direction than predicting acid/base characteristics on color.

 

[MT_Keg]

Understood. The only reason why I ask is that I ran into a situation where my Breiss Brewers Malt ended up having a DI pH of 5.4 which is significantly different than the DI pH referred to in The Water Engine and MME. I understand every maltster is different.

As we discussed already, it would benefit the brewing community to have a database (or a thread) that tracked the data individual brewers are seeing (e.g., maltster, malt type, DI pH, a1, a2, and a3) and include the literature data.

Does the Water Engine take into account the pH shift with Grist Ratio; in some instances I mash as low as 1 qt/lb or as high as 2.5 qt/lb?

The spreadsheet is solid. I appreciate all your hard work.

 

[Silver_Is_Money]

Does the Water Engine take into account the pH shift with Grist Ratio; in some instances I mash as low as 1 qt/lb or as high as 2.5 qt/lb?

Since any acidity which is present within any fixed weight of grist is likewise a fixed commodity (since it hails from the grist), and also since the system is buffered, over the entire span of normally encountered mash dilution ratios there is essentially little to no mash pH shift due solely to the water to grist ratio. This is particularly true for DI water mashes (wherein for mineralized mash water one must consider and manage the molar quantity of present minerals and alkalinity). To imply otherwise would be to imply that as one mashes is less and less water (in a lower water to grist ratio environment), more and more mash pH lowering acidity mysteriously springs from out of nowhere and manifests itself within the mash water/wort. Only one spreadsheet (to my knowledge) operates within the confines of this faulty assumption and demonstrably lowers its mash pH prediction as the water to grist ratio is reduced (and visa-versa).

 

[Paulaner]

Thanks for all you do on these spreadsheets, I still can't live without your brewing sheet. This is working great on my Mac I could never get your brewing engine lite you put out in November to work, but this one immediately worked. Quick question when I put the lactic acid at 1.7% which is what my Sauergut is at and I add 180ml, my mash PH is coming out at 3.269PH am I missing something or should I be entering the data differently.

 

[Big Monk]

Thanks for all you do on these spreadsheets, I still can't live without your brewing sheet. This is working great on my Mac I could never get your brewing engine lite you put out in November to work, but this one immediately worked. Quick question when I put the lactic acid at 1.7% which is what my Sauergut is at and I add 180ml, my mash PH is coming out at 3.269PH am I missing something or should I be entering the data differently.

You can’t model Sauergut as Lactic acid in this system.

I have not incorporated Sauergut yet.

 

[Paulaner]

You can’t model Sauergut as Lactic acid in this system.

I have not incorporated Sauergut yet.

Rodge, I thought I could cheat the system. Thanks again for everything you do here! I try to be a sponge to all your wisdom on this topic.

 

[Big Monk]

Rodge, I thought I could cheat the system. Thanks again for everything you do here! I try to be a sponge to all your wisdom on this topic.

Two things:

1.) I prefer to thought of as a facilitator. This isn’t my work per se, i.e. the calcs are A.J. deLange’s and all of the malt titration info is not mine. If I can take credit for anything it’s that I have a very distinctive formatting style for spreadsheets. That’s it really. None of the hard work is mine.

2.) Use 60 ml/kg of malt of Sauergut in the mash per 0.1 pH drop desired. I’ll try and get the Sauergut working in there by tomorrow.

 

[Paulaner]

Two things:

1.) I prefer to thought of as a facilitator. This isn’t my work per se, i.e. the calcs are A.J. deLange’s and all of the malt titration info is not mine. If I can take credit for anything it’s that I have a very distinctive formatting style for spreadsheets. That’s it really. None of the hard work is mine.

2.) Use 60 ml/kg of malt of Sauergut in the mash per 0.1 pH drop desired. I’ll try and get the Sauergut working in there by tomorrow.

One other question, I'm still using Antioxin SBT should I add this in as Sodium and Powder?

 

[Big Monk]

One other question, I'm still using Antioxin SBT should I add this in as Sodium and Powder?

I had removed it because I wasn’t sure people were really using SBT anymore. I can add it back in tomorrow.

 

[eric19312]

Everything seems to be working for me now and I'm excited to give it a test this weekend. It is calling for significantly more lactic acid than I would normally use based on Bru'n water. 12 mL instead of 0 for this 15 gallon recipe.

I'm wishing you had option to use source water for sparge instead of assumption that I'm using RO for sparge. My source water is pretty soft so I've never bothered getting into RO. But without that toggle I don't see how to figure out my finished water profile...this is important to me for understanding salt impact on flavor. I suppose I might be able to figure out some set of salts that would turn RO water into my base water and then add those to the kettle addition. Off to try that now...

 

[Big Monk]

Everything seems to be working for me now and I'm excited to give it a test this weekend. It is calling for significantly more lactic acid than I would normally use based on Bru'n water. 12 mL instead of 0 for this 15 gallon recipe.

I'm wishing you had option to use source water for sparge instead of assumption that I'm using RO for sparge. My source water is pretty soft so I've never bothered getting into RO. But without that toggle I don't see how to figure out my finished water profile...this is important to me for understanding salt impact on flavor. I suppose I might be able to figure out some set of salts that would turn RO water into my base water and then add those to the kettle addition. Off to try that now...

Sparge acidification and source water as sparge water is in the works. I may be able to get it done tomorrow.

 

[Big Monk]

Understood. The only reason why I ask is that I ran into a situation where my Breiss Brewers Malt ended up having a DI pH of 5.4 which is significantly different than the DI pH referred to in The Water Engine and MME. I understand every maltster is different.

As we discussed already, it would benefit the brewing community to have a database (or a thread) that tracked the data individual brewers are seeing (e.g., maltster, malt type, DI pH, a1, a2, and a3) and include the literature data.

Does the Water Engine take into account the pH shift with Grist Ratio; in some instances I mash as low as 1 qt/lb or as high as 2.5 qt/lb?

The spreadsheet is solid. I appreciate all your hard work.

What you would do in that case is select American 2-Row but override the malt classes DI pH in the grain input section. Simply override the DI pH with 5.4!

That’s one advantage of my sheet over some others, IMHO. You can override the malt classes stock pH DI without override the titration co-efficients. Some programs that give a pH DI override also override the color based acidity proxy and don’t give the full treatment.

 

[Big Monk]

Everything seems to be working for me now and I'm excited to give it a test this weekend. It is calling for significantly more lactic acid than I would normally use based on Bru'n water. 12 mL instead of 0 for this 15 gallon recipe.

I'm wishing you had option to use source water for sparge instead of assumption that I'm using RO for sparge. My source water is pretty soft so I've never bothered getting into RO. But without that toggle I don't see how to figure out my finished water profile...this is important to me for understanding salt impact on flavor. I suppose I might be able to figure out some set of salts that would turn RO water into my base water and then add those to the kettle addition. Off to try that now...

v1.03 (uploaded this morning) added the ability to use Tap/Source water as your Sparge water including a dilution capability as well. All the minerals are tracked in the Pre-Boil water profile.

For the acid concern, can you forward me your water profile and grain bill? I'll give it a whirl so you have a peer check.

 

[Big Monk]

One other question, I'm still using Antioxin SBT should I add this in as Sodium and Powder?

SBT is now back in for v1.03. Still working on Sauergut.

 

[Paulaner]

SBT is now back in for v1.03. Still working on Sauergut.

Sweet, Thanks!

 

[MT_Keg]

Can you identify the version number within the Spreadsheet in addition to the file name? Maybe even keep track of the revision history in another tab of the file. I often change the file name to my recipe name and date brewed which will remove the version number in the file name.

 

[Big Monk]

Can you identify the version number within the Spreadsheet in addition to the file name? Maybe even keep track of the revision history in another tab of the file. I often change the file name to my recipe name and date brewed which will remove the version number in the file name.

The revision history is tracked on the website. I'll add version numbers to current sheets.

 

[ScrewyBrewer]

@RPIScotty I was able to do a side by side comparison today of your Water Engine and my ezRecipe 2.01 calculations. Here is what I found.

I started out using my RO water profile as 100% of the (12 gallons/45.43 liters) of strike water.

The mash pH prediction using the Briess grains below is 5.57 using the Water Engine DIpH override values. Edited
(pH prediction method based on Kai Troester 2012)

The mash pH prediction using the Briess grains below is 5.67 using the Riffe formula.
(pH prediction method based on Kai Troester 2012)

The mash pH prediction using the same Briess grains is 5.58 using color-based formula.
(pH prediction method based on Kai Troester 2009)

The mash pH prediction entering the same type of grains in Water Engine is 5.53 using the Gen II formula. Edited

A nice new feature of ezRecipe 2.01 lets me select between 3 different pH mash prediction methods. Making these comparisons easy to do.

 

[Big Monk]

@RPIScotty I was able to do a side by side comparison today of your Water Engine and my ezRecipe 2.01 calculations. Here is what I found.

I started out using my RO water profile as 100% of the (12 gallons/45.43 liters) of strike water.
View attachment 610350

The mash pH prediction using the Briess grains below is 5.66 using the Water Engine DIpH override values.
(pH prediction method based on Kai Troester 2012)
View attachment 610340
View attachment 610342

The mash pH prediction using the Briess grains below is 5.67 using the Riffe formula.
(pH prediction method based on Kai Troester 2012)
View attachment 610345
View attachment 610346

The mash pH prediction using the same Briess grains is 5.58 using color-based formula.
(pH prediction method based on Kai Troester 2009)
View attachment 610343


The mash pH prediction entering the same type of grains in Water Engine is 5.62 using the Gen II formula.
View attachment 610347
View attachment 610348

A nice new feature of ezRecipe 2.01 lets me select between 3 different pH mash prediction methods. Making these comparisons easy to do.

That wheat/rye DI pH in my sheet looks suspect. 6.61 has to be an error.

 

[ScrewyBrewer]

That wheat/rye DI pH in my sheet looks suspect. 6.61 has to be an error.

After replacing Flaked Wheat/Rye with Dark Crystal malt type for the Crystal 90L in my recipe as I wish I did in the first place the Water Engine predicted a mash pH of 5.53. So then I went back and corrected my original post to show the new results.

 

[Big Monk]

After replacing Flaked Wheat/Rye with Dark Crystal malt type for the Crystal 90L in my recipe as I wish I did in the first place the Water Engine predicted a mash pH of 5.53. So then I went back and corrected my original post to show the new results.

But you helped expose an obvious error with the flaked wheat DI pH! Thank you!

 

[ScrewyBrewer]

But you helped expose an obvious error with the flaked wheat DI pH! Thank you!

You're welcome. I'm hoping you'll do the same for me!

 

[Big Monk]

You're welcome. I'm hoping you'll do the same for me!

I just recently did a full revision of the malt classes and must have fat fingered flaked wheat/rye.

EDIT: I double checked Riffe’s paper and 6.61 for Flaked Wheat/Rye is accurate.

 

[Paulaner]

I'm kind of thinking out loud and more curiosity than anything, but how much affect does our inaccurate inputs of malt have on these calculators. For example, I know the DI PH of my malts that I purchase in bulk as I get all the data from Weyermann, but for example the Cara malt or Roast malt that I use a few percentages of in my grain bill, I don't have the slightest clue of what the specs are other than a broad range of the color. I remember AJ posting that it's such a small percentage of our bill that we'll get in the ballpark of. Again not looking for any answers I'm just thinking out loud.

 

[Big Monk]

I'm kind of thinking out loud and more curiosity than anything, but how much affect does our inaccurate inputs of malt have on these calculators. For example, I know the DI PH of my malts that I purchase in bulk as I get all the data from Weyermann, but for example the Cara malt or Roast malt that I use a few percentages of in my grain bill, I don't have the slightest clue of what the specs are other than a broad range of the color. I remember AJ posting that it's such a small percentage of our bill that we'll get in the ballpark of. Again not looking for any answers I'm just thinking out loud.

I can’t really be sure. Part of the reason I went with the malt classes was to take the inputs out of the users hands and replace them with decent estimates for DI pH and titration. That way we can at least control them and limit user error until we see how this new model performs.

 

[Big Monk]

v1.04 went up this morning.

I rearranged some things in the interface.

 

[Big Monk]

v1.05 just went up.

I fixed a few errors:

1.) Nitrate was left out of the ion balance for some reason.

2.) I was including both the Diluted source and mash water concentrations of Ca and Mg in their respective Q calculations. Since the concentration of the diluted source is included in the mash concentration, I was inflating the Q values for both.

The Water Engine will likely be phased out in the next month or so as I revise the old Low Oxygen Brewing spreadsheet to include The Water Engine. No worries to those not using LOB techniques as it will be a viable option for all brewers. This new sheet will be called The Brewing Engine.

 

[ScrewyBrewer]

@RPIScotty thank you for your dedication and for sharing your work! I'm looking forward to the availability of The Brewing Engine.

 

[Big Monk]

@RPIScotty thank you for your dedication and for sharing your work! I'm looking forward to the availability of The Brewing Engine.

I had a chance to work on the "System Settings" tab last night:

 

[ScrewyBrewer]

I see you're making steady progress on The Brewing Engine @RPIScotty nicely done.

 

[Big Monk]

I issued v1.07 of The Water Engine this morning.

Key changes:

1.) There was an error in the % based input sheet where lbm values for grains wasn't populating because of a bad reference.

2.) I added a macro button that solves for Alkalinity as CaCO3 in the source water by zeroing out the Cation to Anion difference value.

 

[Big Monk]

v.1.08 of The Water Engine is up.

i was doing some analysis for someone and found an error in the way the background functions were calculating the Anion totals.

 

[belgbrew]

Cannot find it anywhere
The website is not working, the old links are also dead
How to get it to try?

 

[Big Monk]

Cannot find it anywhere
The website is not working, the old links are also dead
How to get it to try?

Link is in my signature.

 

[balrog]

Cannot find it anywhere
The website is not working, the old links are also dead
How to get it to try?

Yes, make sure to use the updated signature "http://brewingengine.yolasite.com/" rather than original "waterengine" from post1; that tripped me up when using my browsing history.

 

[Big Monk]

For all those not using Excel whomay have trouble with Macros working, etc., Ihave created a Macro free version.

Visit the site in my signature.

It has a very limited feature set without the solver and macro buttons but it still works like a charm, if not less intuitively.

 

[Big Monk]

This morning I took down the solver-free Water Engine and replaced it with a modified version of the deLange Water Troubleshooter/Voltmeter which A.J. shared in our huge water talk thread last summer. It was the original sheet that sparked a lot of the innovations of the last year and while if has background functions and is a macro-enabled sheet, it is manual and doesn't use the solver.

A good tool for those without Excel or who are just looking to get a handle on the equations and theory.

 

[Big Monk]

Uploaded v1.09 this morning. Very minor formatting changes and I eliminated the Sauermalz button from the percentage sheet. It was a wonky setup that only I could really manipulate with much explanation.

Making progress on The Brewing Engine as well but it is slow going as I properly implement the unit selectors.

 

[TheMadKing]

I had a chance to work on the "System Settings" tab last night:

View attachment 617361

It looks great!

Is there any chance it's compatible with excel for Mac?

I know the VBA functions are slightly different which is obnoxious.

 

[Big Monk]

It looks great!

Is there any chance it's compatible with excel for Mac?

I know the VBA functions are slightly different which is obnoxious.

I don't believe it will be compatible. Excel for Mac is a different beast.