The Water Engine

[balrog]

Never mind.
Problem exists between keyboard and chair.

 

[Big Monk]

Calc Mash button works to show changes with grist and salts
Enter 5.4 and Calc Acid, leaves it zero.
Up Sourmaltz so that Calc Mash shows < 5.4 and Calc Soda/Lime leave those as zero

Excel 2016, Win7/64; Solver addin, trust macro and tools/ref Solver in VBA

Nothing you did wrong. I forgot to change the references in the VBA for the ML version. Should be good now.

 

[balrog]

Nothing you did wrong. I forgot to change the references in the VBA for the ML version. Should be good now.

In M vers, cell X46 is "=SUM($X$28:$X$36)" I think you mean =SUM($X$37:$X$45)

 

[Big Monk]

In M vers, cell X46 is "=SUM($X$28:$X$36)" I think you mean =SUM($X$37:$X$45)

You might not have pulled a new version. X46 = SUM($X$37:$X$45) in my recent upload.

 

[balrog]

I just changed the spreadsheet refs, and the VBA functions , $X$something to $X$46 in the baksod/lime/ph functions and got it all sorted.

 

[Big Monk]

I just changed the spreadsheet refs, and the VBA functions , $X$something to $X$46 in the baksod/lime/ph functions and got it all sorted.

What I'm saying is that is all fixed in the recent upload.

Glad you got it squared away.

 

[balrog]

I apologize, didn't mean to suggest otherwise, I am sure I was too quick to download. This is quite impressive and I really appreciate your efforts.

I will be doing a 75/25% Dingemans Pils/AmerWhiteWheat this weekend and trying my new pH meter. The last two batches I pulled samples late from the mash, 45m and 30m. I will try to get a 15m sample this weekend.

Thanks again. This is awesome.

 

[MT_Keg]

The Equation in Column J, Rows 5 through 13 should be updated to divide by K14.

 

[Big Monk]

The Equation in Column J, Rows 5 through 13 should be updated to divide by K14.

What sheet are you using? The mass or % based sheet?

EDIT: I see it. Updated. Nice catch.

 

[MT_Keg]

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

 

[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.