The Water Engine

HomeBrewTalk.com - Beer, Wine, Mead, & Cider Brewing Discussion Community.

Help Support Homebrew Talk:

Big Monk

Trappist Please! 🍷
Joined
Dec 24, 2015
Messages
2,193
Reaction score
1,125
I asked the HBT admin staff to help me retire my old post:

https://www.homebrewtalk.com/forum/threads/new-brewing-software.653060/

so that I could reintroduce a spreadsheet I've been working on. It's called "The Water Engine" and it's based on the background functions developed by A.J. deLange and incorporates my typical spreadsheet formatting and the Excel Solver. The Mass based and Percentage based grain input versions, along with help documents on enabling both the Excel Solver and Macros can be found here:

https://waterengine.yolasite.com/

The sheet is based in Metric, as all the background calculations are based in Metric, but i included a handy little conversion table in each sheet for people to use to convert the few important inputs needed from Standard to Metric.

I hope it turns out to be useful. Please report any issues you encounter directly to me. It is a simple sheet so any problems will be quick fixes i'm sure.

Enjoy!
 

Silver_Is_Money

Larry Sayre, Developer of 'Mash Made Easy'
Joined
Dec 31, 2016
Messages
5,632
Reaction score
1,709
Location
N/E Ohio
I'm sure it is very nice in Excel, but in LiberOffice 6.0 I'm receiving a "Basic Runtime Error '35' SolverOK" message, and then it chokes. Should I try running it in Google Sheets?
 
OP
Big Monk

Big Monk

Trappist Please! 🍷
Joined
Dec 24, 2015
Messages
2,193
Reaction score
1,125
I'm sure it is very nice in Excel, but in LiberOffice 6.0 I'm receiving a "Basic Runtime Error '35' SolverOK" message, and then it chokes. Should I try running it in Google Sheets?
Sounds like Libre can't run the solver. I almost certain Google sheets doesn’t have that functionality.
 
Last edited:
OP
Big Monk

Big Monk

Trappist Please! 🍷
Joined
Dec 24, 2015
Messages
2,193
Reaction score
1,125
Ooh shiny!! Me likey! Runs fine on my windows 10 machine!!!
Let me know how it works out. Obviously the concept is a work in progress but I’d love to have people report real life results.

If the response is good I have some things I removed for the release that I can add back in for greater flexibility, namely more macro buttons for Sauermalz, Acid, Baking Soda, and Pickling Lime that allow for more precise calculations and troubleshooting.
 
Last edited:

Jayjay1976

Bubblegazer
Joined
Nov 26, 2016
Messages
3,568
Reaction score
2,877
Location
Chicago
Let me know how it works out. Obviously the concept is a work in progress but I’d love to have people report real life results.

If the response is good I have some things I removed for the release that I can add back in for greater flexibility, namely more macro buttons for Sauermalz, Acid, Baking Soda, and Pickling Lime that allow for more precise calculations and troubleshooting.
I'm going to run one of my latest recipes through it as a proof of concept check before brewing it next weekend. Thanks for being so supremely knowledgeable and entirely willing to share your expertise. It is greatly appreciated!
 
OP
Big Monk

Big Monk

Trappist Please! 🍷
Joined
Dec 24, 2015
Messages
2,193
Reaction score
1,125
I'm going to run one of my latest recipes through it as a proof of concept check before brewing it next weekend. Thanks for being so supremely knowledgeable and entirely willing to share your expertise. It is greatly appreciated!
Don’t thank me, thank A.J. deLange.

I just turned it into an attractive spreadsheet.
 
Last edited:

ScrewyBrewer

ezRecipe - Beer Recipe Design Made Easy!
Lifetime Supporter
Joined
Jun 5, 2010
Messages
1,801
Reaction score
418
Location
New Jersey
Very nice job Derek! It works perfectly on my Windows 7 machine. Microsoft Excel on Windows or Mac is my platform of choice. I don't know of any other type of spreadsheet that supports macro programming that's compatible with the Microsoft visual basic language. If there are any I'd like to know what they are. I'll be able to run some side by side comparisons with two of my recipes later this week.
 
OP
Big Monk

Big Monk

Trappist Please! 🍷
Joined
Dec 24, 2015
Messages
2,193
Reaction score
1,125
Very nice job Derek! It works perfectly on my Windows 7 machine. Microsoft Excel on Windows or Mac is my platform of choice. I don't know of any other type of spreadsheet that supports macro programming that's compatible with the Microsoft visual basic language. If there are any I'd like to know what they are. I'll be able to run some side by side comparisons with two of my recipes later this week.
I updated the sheet this morning to v1.01 and included some more Macro buttons for Solving for Acid, Baking Soda, Pickling Lime, and Sauermalz (unfortunately only for the % based sheet so far).

I also drafted a tutorial on using the buttons and how they can interact.
 
Last edited:
OP
Big Monk

Big Monk

Trappist Please! 🍷
Joined
Dec 24, 2015
Messages
2,193
Reaction score
1,125
I'm sure it is very nice in Excel, but in LiberOffice 6.0 I'm receiving a "Basic Runtime Error '35' SolverOK" message, and then it chokes. Should I try running it in Google Sheets?
I'm going to download Libre Office a little later at home and see if i can't trouble shoot it and get it to work for you.
 

balrog

Supporting Member
HBT Supporter
Joined
Nov 26, 2013
Messages
3,817
Reaction score
3,545
Small sidenote: Excel 2007 chokes, even with macros/trust enabled as per PDF instruction files. Gives a Compile Error in Hidden Module: Module1.

But it works fine in Excel 2016.

ED: This is on Windows7/64
 

Silver_Is_Money

Larry Sayre, Developer of 'Mash Made Easy'
Joined
Dec 31, 2016
Messages
5,632
Reaction score
1,709
Location
N/E Ohio
I'm going to download Libre Office a little later at home and see if i can't trouble shoot it and get it to work for you.
To assist your efforts here: A linear only version of Solver comes standard with LibreOffice Calc. If needed, there is a non-linear Solver available as a download and install option. I have this option installed.
 
OP
Big Monk

Big Monk

Trappist Please! 🍷
Joined
Dec 24, 2015
Messages
2,193
Reaction score
1,125
To assist your efforts here: A linear only version of Solver comes standard with LibreOffice Calc. If needed, there is a non-linear Solver available as a download and install option. I have this option installed.
I don't think the issue is solver per se.

I just messed around and it looks as if Libre Office can't handle the Solver VBA code that executes with the push buttons.

Looks like you may be out of luck Larry.
 

balrog

Supporting Member
HBT Supporter
Joined
Nov 26, 2013
Messages
3,817
Reaction score
3,545
For us lazynonmetricheads, you can link J4-J12 to X24-X32 and enter your pounds on W24-W32
Likewise R4,6,8,9,11 to X13, X20, X14, X15, X16
 
OP
Big Monk

Big Monk

Trappist Please! 🍷
Joined
Dec 24, 2015
Messages
2,193
Reaction score
1,125
For us lazynonmetricheads, you can link J4-J12 to X24-X32 and enter your pounds on W24-W32
Likewise R4,6,8,9,11 to X13, X20, X14, X15, X16
I’d say it’s simple enough as is. Call it your mental gymnastics for the day!
 
OP
Big Monk

Big Monk

Trappist Please! 🍷
Joined
Dec 24, 2015
Messages
2,193
Reaction score
1,125
Small sidenote: Excel 2007 chokes, even with macros/trust enabled as per PDF instruction files. Gives a Compile Error in Hidden Module: Module1.

But it works fine in Excel 2016.

ED: This is on Windows7/64
I’ll keep an eye out. I’m wondering if that is due to the VBA being protected.
 

eric19312

Supporting Member
HBT Supporter
Joined
Dec 5, 2012
Messages
3,462
Reaction score
1,927
Location
Long Island
I am getting a compile error...
using excel version 16.19 for mac

happens when I click the solve button. I did make sure solver add-in is on.
Any suggestions?

upload_2019-1-29_11-30-29.png
 
OP
Big Monk

Big Monk

Trappist Please! 🍷
Joined
Dec 24, 2015
Messages
2,193
Reaction score
1,125
I am getting a compile error...
using excel version 16.19 for mac

happens when I click the solve button. I did make sure solver add-in is on.
Any suggestions?

View attachment 609700
I don't believe the Macro Solver buttons work on the Mac version.

When you hit ALT+F11, can you access the References in the VBA screen?

EDIT: I went ahead and unlocked the VBA file. I did not have it locked for any secrecy reasons, as the functions aren't mine to protect, etc. (A.J. developed them) but more because I didn't want to have people messing around back there!

People should be able to access Tools - References - Check "Solver" box, which i think may be causing some issues.

I updated the files at the site as well as the Enabling Solver PDF.
 
Last edited:
OP
Big Monk

Big Monk

Trappist Please! 🍷
Joined
Dec 24, 2015
Messages
2,193
Reaction score
1,125
For those experiencing issues getting the solver to work, I suggest downloading the revised "Enabling the Excel Solver PDF" and making sure that Solver is enabled in the references of the VBA code as well.

The entire sheet and VBA code is unprotected so you should have no issues doing so.
 

ScrewyBrewer

ezRecipe - Beer Recipe Design Made Easy!
Lifetime Supporter
Joined
Jun 5, 2010
Messages
1,801
Reaction score
418
Location
New Jersey
I updated the sheet this morning to v1.01 and included some more Macro buttons for Solving for Acid, Baking Soda, Pickling Lime, and Sauermalz (unfortunately only for the % based sheet so far).

I also drafted a tutorial on using the buttons and how they can interact.
Perfect, I've downloaded v1.01 and the accompanying tutorial.
 
OP
Big Monk

Big Monk

Trappist Please! 🍷
Joined
Dec 24, 2015
Messages
2,193
Reaction score
1,125
Perfect, I've downloaded v1.01 and the accompanying tutorial.
I'm working on some "improvements" to the malt classes and their pH DI and titration co-efficient data today.
 

eric19312

Supporting Member
HBT Supporter
Joined
Dec 5, 2012
Messages
3,462
Reaction score
1,927
Location
Long Island
I don't believe the Macro Solver buttons work on the Mac version.

When you hit ALT+F11, can you access the References in the VBA screen?

EDIT: I went ahead and unlocked the VBA file. I did not have it locked for any secrecy reasons, as the functions aren't mine to protect, etc. (A.J. developed them) but more because I didn't want to have people messing around back there!

People should be able to access Tools - References - Check "Solver" box, which i think may be causing some issues.

I updated the files at the site as well as the Enabling Solver PDF.

Version 1.01 seems to be working on the mac. At least I can enter values and calculate a pH. I'm seeing very different prediction than I get with Bru'n water but am not sure I have all the inputs right. I am concerned that your ion vs anion calculator is showing my water input has much higher anions than cations so think either data entry error or calculation error. Here is what I see on water engine:

upload_2019-1-30_9-27-23.png


And here is same information entered into Bru'n water:
upload_2019-1-30_9-28-55.png


Any advice?

I figured out the acid solver function after going back to the written guide. That part about the mash pH being also a user input cell is really very cool but was not intuitive to me. I think a comment in the worksheet would of helped...
 
Last edited:
OP
Big Monk

Big Monk

Trappist Please! 🍷
Joined
Dec 24, 2015
Messages
2,193
Reaction score
1,125
Version 1.01 seems to be working on the mac. At least I can enter values and calculate a pH. I'm seeing very different prediction than I get with Bru'n water but am not sure I have all the inputs right. I am concerned that your ion vs anion calculator is showing my water input has much higher anions than cations so think either data entry error or calculation error. Here is what I see on water engine:

View attachment 609829

And here is same information entered into Bru'n water:
View attachment 609830

Any advice?

I figured out the acid solver function after going back to the written guide. That part about the mash pH being also a user input cell is really very cool but was not intuitive to me. I think a comment in the worksheet would of helped...
Let me take a look...
 
OP
Big Monk

Big Monk

Trappist Please! 🍷
Joined
Dec 24, 2015
Messages
2,193
Reaction score
1,125
Version 1.01 seems to be working on the mac. At least I can enter values and calculate a pH. I'm seeing very different prediction than I get with Bru'n water but am not sure I have all the inputs right. I am concerned that your ion vs anion calculator is showing my water input has much higher anions than cations so think either data entry error or calculation error. Here is what I see on water engine:

View attachment 609829

And here is same information entered into Bru'n water:
View attachment 609830

Any advice?

I figured out the acid solver function after going back to the written guide. That part about the mash pH being also a user input cell is really very cool but was not intuitive to me. I think a comment in the worksheet would of helped...
All squared away. I had an extra term in the Anion calculation.

Also, v1.02 is uploaded. I made some changes to the malt equation and re-organized some stuff. Also put a note in the mash pH cell making it explicit that it's a user input.
 
OP
Big Monk

Big Monk

Trappist Please! 🍷
Joined
Dec 24, 2015
Messages
2,193
Reaction score
1,125
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.
 
OP
Big Monk

Big Monk

Trappist Please! 🍷
Joined
Dec 24, 2015
Messages
2,193
Reaction score
1,125
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

Supporting Member
HBT Supporter
Joined
Nov 26, 2013
Messages
3,817
Reaction score
3,545
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.
 
OP
Big Monk

Big Monk

Trappist Please! 🍷
Joined
Dec 24, 2015
Messages
2,193
Reaction score
1,125
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

Supporting Member
HBT Supporter
Joined
Nov 26, 2013
Messages
3,817
Reaction score
3,545
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

Well-Known Member
Joined
Jan 4, 2013
Messages
153
Reaction score
33
Location
Knoxville
The Equation in Column J, Rows 5 through 13 should be updated to divide by K14.
 
OP
Big Monk

Big Monk

Trappist Please! 🍷
Joined
Dec 24, 2015
Messages
2,193
Reaction score
1,125
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

Well-Known Member
Joined
Jan 4, 2013
Messages
153
Reaction score
33
Location
Knoxville
No problem! I guess I have a silly question. Where did the information come from to populate the malt class database?
 
Top