The Water Engine

Homebrew Talk - Beer, Wine, Mead, & Cider Brewing Discussion Forum

Help Support Homebrew Talk - Beer, Wine, Mead, & Cider Brewing Discussion Forum:

This site may earn a commission from merchant affiliate links, including eBay, Amazon, and others.

Big Monk

Trappist Please! 🍷
Joined
Dec 24, 2015
Messages
2,192
Reaction score
1,151
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!
 
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 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:
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:
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!
 
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:
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.
 
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:
Just downloaded. Very excited.
giphy.gif
 
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.
 
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'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.
 
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.
 
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
 
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!
 
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.
 
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
 
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:
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.
 
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.
 
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:
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...
 
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.
 
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.
 
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.
 
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.
 
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.
 
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.
 
No problem! I guess I have a silly question. Where did the information come from to populate the malt class database?
 
Back
Top