New Brewing Software

[Big Monk]

I’d like to announce the fruit of a recent collaboration between A.J. deLange and I which I’ve tentatively titled “The deLange-Scott Brewing Engine”.

It is a brewing spreadsheet which incorporates the proton deficit/charge accounting method of pH estimation long lauded by A.J.. Essentially it is a second generation pH estimation algorithm that finally leaves behind color based acidity approximations and other assumptions made by readily available water chemistry software and implements a technically and scientifically sound pH estimation method. In addition, it also allows for recipe input, bitterness estimation, color estimation, volume tracking and equipment profile, and extract estimation (including either No-Sparge or Batch Sparge).

It incorporates the common Low Oxygen brewing features such as metabisulfite dosing for mash and sparge water and Morey equation modifier for accounting for the lack of color pickup in the presence of oxygen.

In addition to a rigorously prepared set of calculations it utilizes the embedded Excel Solver and Macro buttons to offer unparalleled user control over the target pH and amounts of acid/base components to be used.

The sheet will be offered for free but protected to safeguard the integrity of the calculations and algorithm.

It should be available sometime next week following peer review by A.J. and the subsequent comment incorporation, error checking, and final formatting.

 

[FunkedOut]

In!
Looking forward to it.
Appreciate you guys sharing your collaboration, work, knowledge and time.

 

[day_trippr]

Hopefully rigorous testing on freely available spreadsheet programs will be done...

Cheers!

 

[shetc]

I’d like to announce the fruit of a recent collaboration between A.J. deLange and I which I’ve tentatively titled “The deLange-Scott Brewing Engine”.

It is a brewing spreadsheet which incorporates the proton deficit/charge accounting method of pH estimation long lauded by A.J.. Essentially it is a second generation pH estimation algorithm that finally leaves behind color based acidity approximations and other assumptions made by readily available water chemistry software and implements a technically and scientifically sound pH estimation method. In addition, it also allows for recipe input, bitterness estimation, color estimation, volume tracking and equipment profile, and extract estimation (including either No-Sparge or Batch Sparge).

It incorporates the common Low Oxygen brewing features such as metabisulfite dosing for mash and sparge water and Morey equation modifier for accounting for the lack of color pickup in the presence of oxygen.

In addition to a rigorously prepared set of calculations it utilizes the embedded Excel Solver and Macro buttons to offer unparalleled user control over the target pH and amounts of acid/base components to be used.

The sheet will be offered for free but protected to safeguard the integrity of the calculations and algorithm.

It should be available sometime next week following peer review by A.J. and the subsequent comment incorporation, error checking, and final formatting.

Can't wait to try it

 

[Big Monk]

Can't wait to try it

It’s 100% functional right now but I still need to do some quality checks, formatting, and make sure implemented the metabisulfite and phosphoric acid inputs correctly.

A.J. has the latest version for peer review on a few things.

Should be on schedule for release sometime next week.

 

[shetc]

It’s 100% functional right now but I still need to do some quality checks, formatting, and make sure implemented the metabisulfite and phosphoric acid inputs correctly.

A.J. has the latest version for peer review on a few things.

Should be on schedule for release sometime next week.

I read the original thread that lead to this new work. If I understood it correctly, aren't there some data that need to collected from the maltsters?

 

[Calaeb]

Sounds awesome

 

[Big Monk]

I read the original thread that lead to this new work. If I understood it correctly, aren't there some data that need to collected from the maltsters?

Yes and no.

Yes, because we’d like to see, in addition to pH DI, the buffering co-efficients a, b, and c you get from titration of a test mash on malt analysis data sheets going forward.

No, because there is now a very sizable data set of measured pH DI values for common malts across all types that have either a, b, and c co-efficients to go with them.

What I did was take titration data that A.J. had from his tests, Joe Walt’s tests and Kai’s tests and combine them with the pH DI and Bg data from D.M. Riffe’s latest paper into an Excel spreadsheet. There were more than 100 examples. I then separated the list into malt types (Pilsner, Pale, Munich, etc.) housed on separate tabs. I followed Riffe’s groupings but expanded them even further into Sub groupings (light crystal, light roast, etc.)

The final piece was analyzing these groups along with my 3 years worth of Weyermann Lot information for pH DI and creating 31 distinct and representative malt “classes” that users select from a dropdown. These classes are representative of certain malt types (European Pilsner, American Pilsner, European Pale Ale, etc.) and have corresponding pH DI, a, b, and c values.

At the very least this new way is better than color approximations because 100% (out of 31) of the malt classes I created are linear (pH DI and a). Where it gets better is that 50% have 2 co-efficients (pH DI, a, and b) and more than 30% have all 3 co-efficients (pH DI, a, b, and c).

Exciting stuff!

 

[shetc]

Yes and no.

Yes, because we’d like to see, in addition to pH DI, the buffering co-efficients a, b, and c you get from titration of a test mash on malt analysis data sheets going forward.

No, because there is now a very sizable data set of measured pH DI values for common malts across all types that have either a, b, and co-efficient to go with them.

What I did was take titration data that A.J. had from his tests, Joe Walt’s tests and Kai’s tests and combine them with the pH DI and Bg data from D.M. Riffe’s latest paper into an Excel spreadsheet. There were more than 100 examples. I then separated the list into malt types (Pilsner, Pale, Munich, etc.) housed on separate tabs. I followed Riffe’s groupings but expanded them even further into European and American types.

The final piece was analyzing these groups along with my 3 years worth of Weyermann Lot information for pH DI and creating 31 distinct and representative malt “classes” that users select from a dropdown. These classes are representative of certain malt types (European Pilsner, American Pilsner, European Pale Ale, etc.) and have corresponding pH DI, a, b, and c values.

At the very least this new way is better than color approximations because 100% (out of 31) of the malt classes I created are linear (pH DI and a). Where it gets better is that 50% have 2 co-efficients (pH DI, a, and b) and more than 30% have all 3 co-efficients (pH DI, a, b, and c).

Exciting stuff!

Awesome work! If you want general QA help then let me know.

 

[Big Monk]

EDIT: Post Error

 

[Big Monk]

Here are the malt classes created for the sheet:

 

[Morrey]

Lots of brilliant thinking has gone into the creation of your software. Thank you in advance and cant wait to give it a try.

 

[Silver_Is_Money]

It will truly be awesome, and I have given my accolades to RPIScotty's efforts both in public and private conversations with him, and I like to think that to at least some small degree it was my urging him along to get him over the hump of indecision that inspired him to get truly motivated by A.J.'s math model, and to subsequently merge it with the genius of the likes of D.M. Riffe, Kai Troester, and others who have gone before.

RPIScotty, once again I can quote the words of wisdom spoken by Issac Newton (the words of inspiration which I initially used in my hopes to motivate you): "If I have seen further, it is because I stood upon the shoulders of giants." You can now proudly join Newton in proclaiming these very words to be your own.

But in fairness, any existing mash pH predicting software worth any level of salt should be capable of gaining a tremendous degree of precision if its level of end user selectable malt class offerings (and each of their independent underlying math model fits to data) were to be similarly broken out into such a fine degree of malt class specificity as you have presented above. It is much easier to gain correlation to data through math models if the models are covering narrower and narrower ranges of malt classifications. To date, most have instead tried to fit their math models across much broader bands of malts and grains in order to offer a degree of simplicity and speed at the sacrifice of precision. You clearly will accept no sacrifice. As to the others, their weight in validity must be witnessed within the quality of their salt.

"Salt is good, but if the salt has lost its saltiness, how will you make it salty again? Have salt in yourselves, and be at peace with one another.” Mark 9:50

 

[Silver_Is_Money]

Just a thought, RPIScotty:

An earlier edition of your software failed in LibreOffice due to its VBA based macros with regard to "Solver". Would you consider modifications to permit those of us who are 100% dedicated to free software such as LiberOffice Calc to be able to use your software? Or will we be compelled to purchase a licensed later version of Microsoft Office in order to utilize your software in Excel?

LibreOffice does have 'Solver'. I have used it successfully with A.J.'s solver spreadsheets.

 

[brewbama]

Lots of brilliant thinking has gone into the creation of your software. Thank you in advance and cant wait to give it a try.

I completely agree.

 

[Big Monk]

Just a thought, RPIScotty:

An earlier edition of your software failed in LibreOffice due to its VBA based macros with regard to "Solver". Would you consider modifications to permit those of us who are 100% dedicated to free software such as LiberOffice Calc to be able to use your software? Or will we be compelled to purchase a licensed later version of Microsoft Office in order to utilize your software in Excel?

LibreOffice does have 'Solver'. I have used it successfully with A.J.'s solver spreadsheets.

Well, yes and no.

Yes, in that I’ll look for a solution to allow you to use the macro buttons in Libre.

No, in that I won’t be removing the button functionality in the sheet, as it’s at the heart of the user friendly aspect.

 

[Big Monk]

It will truly be awesome, and I have given my accolades to RPIScotty's efforts both in public and private conversations with him, and I like to think that to at least some small degree it was my urging him along to get him over the hump of indecision that inspired him to get truly motivated by A.J.'s math model, and to subsequently merge it with the genius of the likes of D.M. Riffe, Kai Troester, and others who have gone before.

RPIScotty, once again I can quote the words of wisdom spoken by Issac Newton (the words of inspiration which I initially used in my hopes to motivate you): "If I have seen further, it is because I stood upon the shoulders of giants." You can now proudly join Newton in proclaiming these very words to be your own.

But in fairness, any existing mash pH predicting software worth any level of salt should be capable of gaining a tremendous degree of precision if its level of end user selectable malt class offerings (and each of their independent underlying math model fits to data) were to be similarly broken out into such a fine degree of malt class specificity as you have presented above. It is much easier to gain correlation to data through math models if the models are covering narrower and narrower ranges of malt classifications. To date, most have instead tried to fit their math models across much broader bands of malts and grains in order to offer a degree of simplicity and speed at the sacrifice of precision. You clearly will accept no sacrifice. As to the others, their weight in validity must be witnessed within the quality of their salt.

"Salt is good, but if the salt has lost its saltiness, how will you make it salty again? Have salt in yourselves, and be at peace with one another.” Mark 9:50

You get a shout out on the instructions page, don’t you worry!

It really was your thread that finally helped click all the puzzle pieces in place for me.

 

[blackelbow]

This sounds awesome. Have you thought about distributing the calculator as a webpage or desktop app instead of a spreadsheet? Utilities like https://www.brewersfriend.com/abv-calculator/ or http://www.data-brew.com/fermtemp/ are super user friendly.

 

[Big Monk]

This sounds awesome. Have you thought about distributing the calculator as a webpage or desktop app instead of a spreadsheet? Utilities like https://www.brewersfriend.com/abv-calculator/ or http://www.data-brew.com/fermtemp/ are super user friendly.

Nope. I prefer Excel for a number of reasons but mostly because I have it, know it well, and it allows me to make the best products I can.

 

[skarp0ye]

This sounds awesome. Have you thought about distributing the calculator as a webpage or desktop app instead of a spreadsheet?

I have offered to incorporate the method/formulas in my brewing app when i saw this post (works on mobile and desktop). But he was not interested at the moment. The option remains open if he changes his mind.

 

[skarp0ye]

Yes and no.
What I did was take titration data that A.J. had from his tests, Joe Walt’s tests and Kai’s tests and combine them with the pH DI and Bg data from D.M. Riffe’s latest paper into an Excel spreadsheet. There were more than 100 examples. I then separated the list into malt types (Pilsner, Pale, Munich, etc.) housed on separate tabs. I followed Riffe’s groupings but expanded them even further into Sub groupings (light crystal, light roast, etc.)

Did you do any verification on the numbers from D.M Riffe's latest paper? As he states he did not verify if they could be used to predict mash pH.

"Unfortunately, the data presented here are not sufficient to reliably predict the pH of a mash made with other than distilled water"

 

[Big Monk]

Did you do any verification on the numbers from D.M Riffe's latest paper? As he states he did not verify if they could be used to predict mash pH.

He didn’t verify but he didn’t need to. The raw material for predicting the acidity of a particular grist consists of pH DI, a, b, and c. Riffe’s paper includes data for pH DI and a, which is sufficient to make a linear model of the malt system.

I took it a few steps further because I had a tracking list from A.J. With 15 or so of his titration measurements (all 3 co-efficient) and about the same amount of Walt’s titration measurements (all 2 co-efficient).

I put all this plus Kai’s measurements (single term like Riffe’s) into a spreadsheet with Riffe’s data (much of which was new) and then separated it all by malt type, until I had 31 distinct malt “classes”, all governed by the over 100 examples of malt titration data.

Then I chose a representative set for each malt class. The advantage of having A.J. 3 term examples was that some of the malt titration data from Riffe and Troester ended up absorbing a second and third buffering term, as well as some of Walt’s data absorbing a third buffering term.

In the end there are 31 malt classes, 31% of which have pH DI, a, b, and c, 50% of which have pH DI, a, and b, and 100% which are at the very least linear around pH DI and a.

That’s the raw material to model the malt system.

We also model the proton deficit/charge of the source water, mineral acids, minerals, etc. So, we can use all this to reliably predict pH.

 

[skarp0ye]

Very interesting, good job. Is A.J.'s data publicly available?

 

[Big Monk]

Very interesting, good job. Is A.J.'s data publicly available?

As far as I know, no.

 

[skarp0ye]

Ok, do let me know if you are willing to share the formulas/calculations/data some time in the future, would love to have this available in my multiplatform application.

 

[Big Monk]

Ok, do let me know if you are willing to share the formulas/calculations/data some time in the future, would love to have this available in my multiplatform application.

Will do but probably not likely. Thank you for the interest though.

 

[Big Monk]

For those considering using this new software, would you prefer to have grain inputs in the percentage based format or mass based format?

 

[Silver_Is_Money]

Weight please.

 

[Biscuits]

Excellent! Looking forward to this!

 

[cyberbackpacker]

Fantastic stuff.

And weight/mass inputs are preferred!

 

[Morrey]

Weight

 

[Big Monk]

Looks like I’ll be releasing a 2 versions, both with a metric/English switch built in, 1 using percentage based recipe input (my preference) and weight/mass based (A.J.’s preference).

Any people want to β test the sheet? I’m looking for people who plan on brewing this week.

 

[Silver_Is_Money]

Looks like I’ll be releasing a 2 versions, both with a metric/English switch built in, 1 using percentage based recipe input (my preference) and weight/mass based (A.J.’s preference).

Any people want to β test the sheet? I’m looking for people who plan on brewing this week.

I'd love to test it, but I'm currently rebuilding our homes primary bathroom, and in the process of ripping the old one out presently, so my focus will be on out with the old and in with the new for awhile. But since I'm not getting younger, "awhile" will likely take well more time than it once would have for me.

 

[DownHillonWater]

Very interested to work with this. I'm a chemist by training and have put a lot of effort into getting my mash pH where I want it Nevertheless there is an empirical component to each new recipe. If you're work can be more accurate I'd love to work with it.

 

[FunkedOut]

I’m planning a brew this week.
Got a brand new MW102 that needs a maiden voyage.
I’ll beta test your sheet.

Mass/weight preferred.

 

[brewbama]

I wish I was brewing this week to check it out. Weight and percent will be great.

 

[pricelessbrewing]

Sounds awesome. I'm glad to see this gain some traction, looking forward to the release and seeing what you put together.

Have you considered google sheets? It uses javascript instead of VBA, has sheets web api, and has addons that can mimic solver (linear, and non linear). Also it's free, and most users already have a google account. Internal functions are nearly identical to excel.

 

[Silver_Is_Money]

Via some add-ons I was able to unlock the non-linear Solver functions in LibreOffice Calc, but it still can't handle the VBA macro coding.

 

[Big Monk]

Sounds awesome. I'm glad to see this gain some traction, looking forward to the release and seeing what you put together.

Have you considered google sheets? It uses javascript instead of VBA, has sheets web api, and has addons that can mimic solver (linear, and non linear). Also it's free, and most users already have a google account. Internal functions are nearly identical to excel.

I don't believe there is any password protection in sheets. It's a bit wonkier than Excel.

 

[pricelessbrewing]

It can be locked to the creator and to a list of individuals the creator chooses.