New Giveaway - Wort Monster Conical Fermenter!

Home Brew Forums > Home Brewing Beer > Brewing Software > Mash Water Chemistry Calculator - Feedback Wanted




Reply
 
LinkBack Thread Tools Display Modes
Old 07-28-2007, 03:44 AM   #1
Bearcat Brewmeister
Pour, Drink, Pee, Repeat
HBT_LIFETIMESUPPORTER.png
Feedback Score: 0 reviews
 
Bearcat Brewmeister's Avatar
Recipes 
 
Join Date: Feb 2006
Location: Cincinnati, OH
Posts: 698
Liked 17 Times on 7 Posts

Default Mash Water Chemistry Calculator - Feedback Wanted

I have been looking at what is available out there and decided to do a little work in Excel and create my own, borrowing some ideas from Palmer's tool and the Water Witch. What I wanted to do is have a place to enter your water chemistry and the chemistry of the water you would like to copy. Based on those two sets of entries, it would then calculate what mash water additions are needed to get as close as possible to the target mash water. In addition, it also figures out how much you need to dilute your water with distilled/RO water if you are too high in some minerals. You can lock additions at a certain level if you wish (zero if you prefer not to use a certain type of additive or a nice round number if you so choose). Another output is a graph that shows the best SRM of beer for your water, the target water, and the water as adjusted.

It runs using Solver to minimize an error function (target minus adjusted water, by ppm of each critical ion) and an Excel macro, so if you download this tool, you will need to enable the macros when asked (no malicious code in there - I can provide the code to anyone who wants to see it and verify). If some of you that use Excel and also have experience in modifying your mash water chemistry can check this out and give me some feedback on how to improve it, I would really appreciate it. If you would like to know more details on how it works behind the scenes or why I chose to do it this way, just let me know.

Download Mash Water Chemistry Calculator



__________________
Kegged: Belgian Dark Strong, Robust Porter, Ryewine, Old Ale
Secondary:
Bock
Primary:
--
Next Up: Baltic Porter, Saison
Projects: Brutus Strut-stand, Freezer Conversion (Done), HERMS (Done), Lager Fermentation Mini-fridge Extension (Done)
Drinking: Great Lakes Edmund Fitzgerald, Fuller's 1845, Lakefront Fixed Gear, New Glarus Moon Man, Tröegs Troegenator
Bearcat Brewmeister is offline
 
Reply With Quote Quick reply to this message
Old 07-28-2007, 04:01 AM   #2
Dude
Will work for beer
HBT_LIFETIMESUPPORTER.png
Feedback Score: 0 reviews
 
Dude's Avatar
Recipes 
 
Join Date: Jan 2005
Location: Knob Noster, Missouri
Posts: 8,843
Liked 33 Times on 25 Posts
Likes Given: 5

Default

That looks absolutely great! I'll play with it a little bit and give you some feedback.

Thanks!



__________________
On Tap: Lake Walk Pale Ale -- Eternity (Raspberry Stout) -- Nutrocker -- Donnybrook Dark
Primary: Lake Walk Pale Ale
Secondary: Summit IPA
Up Next: Smoked Porter -- Pub Ale -- Watermelon Wheat
Planning:
Gone But Not Forgotten:

www.IronOrrBrewery.com
Dude is offline
 
Reply With Quote Quick reply to this message
Old 07-28-2007, 01:42 PM   #3
FSR402
HBT_LIFETIMESUPPORTER.png
Feedback Score: 0 reviews
 
FSR402's Avatar
Recipes 
 
Join Date: May 2007
Location: Jenison, MI
Posts: 2,876
Liked 13 Times on 11 Posts
Likes Given: 1

Default

Man that is sweet. Not that I understand any of it but it's still cool. Will come in handy once I know what I'm doing.

__________________
TWO FISTED BREWING CO.

Quote:
Originally Posted by mr_cad View Post
Its nice when you and your friends have comparable equipment.
Quote:
Originally Posted by Walker-san View Post
some people will tap anything that has a hole.
Buy Two Fisted Brewing gear
FSR402 is offline
 
Reply With Quote Quick reply to this message
Old 07-28-2007, 03:25 PM   #4
johnsma22
Feedback Score: 0 reviews
 
johnsma22's Avatar
Recipes 
 
Join Date: Jun 2006
Location: Taunton, MA
Posts: 1,890
Liked 32 Times on 26 Posts

Default

It will not open in Google Docs, and when I attempt to open it in Excel I get "run-time error 9/subscript out of range", and Excel wants to go into debug mode.

__________________
Cheers,
John
johnsma22 is offline
 
Reply With Quote Quick reply to this message
Old 07-28-2007, 07:42 PM   #5
Madtown Brew
Feedback Score: 0 reviews
Recipes 
 
Join Date: Jun 2007
Location: Madison, WI
Posts: 259
Default

Tried to run this today. After enabling the macros, here's what I got:

Quote:
Run-time error '1004'

Unable to set the Installed property of the AddIn class

debug mode takes me to a window with this:


Quote:
Private Sub Workbook_Open()
AddIns("Solver Add-In").Installed = True
On Error Resume Next
Application.VBE.ActiveVBProject.References.AddFrom File (Application.LibraryPath & "solver.xla")
End Sub
Any idea how to fix? I don't use excel often, so I'm kind of lost here.
Sure would be a great tool to have.
__________________
Fermenting:
rye farmhouse ale
Oktoberfest
flanders red
Kegged:
Oktoberfest
Madtown Brew is offline
 
Reply With Quote Quick reply to this message
Old 07-28-2007, 09:15 PM   #6
FlyGuy
Feedback Score: 0 reviews
 
FlyGuy's Avatar
Recipes 
 
Join Date: Jan 2007
Location: Calgary, Alberta
Posts: 3,618
Liked 122 Times on 40 Posts
Likes Given: 7

Default

Quote:
Originally Posted by johnsma22
It will not open in Google Docs, and when I attempt to open it in Excel I get "run-time error 9/subscript out of range", and Excel wants to go into debug mode.
It did the same thing for me. But then I realized that I didn't have the Solver Add-in installed. You will have to go back to your Microsoft Office installation and enable the add-in.
__________________
Cheap 10 gal cooler MLT$3 AutosiphonAluminum Pot FAQEasy Steam Injection Mash SystemMake a Frozen Yeast Bank
Improving Stovetop Boiling Improving AG Efficiency
FlyGuy is offline
 
Reply With Quote Quick reply to this message
Old 07-28-2007, 11:53 PM   #7
Madtown Brew
Feedback Score: 0 reviews
Recipes 
 
Join Date: Jun 2007
Location: Madison, WI
Posts: 259
Default

Quote:
Originally Posted by FlyGuy
It did the same thing for me. But then I realized that I didn't have the Solver Add-in installed. You will have to go back to your Microsoft Office installation and enable the add-in.
That's likely what's wrong with mine as well.
I guess it pays to read the OP carefully:

Quote:
It runs using Solver to minimize an error function (target minus adjusted water, by ppm of each critical ion) and an Excel macro, so if you download this tool, you will need to enable the macros when asked
<goes rummaging into the closet to find the Office CD>
__________________
Fermenting:
rye farmhouse ale
Oktoberfest
flanders red
Kegged:
Oktoberfest
Madtown Brew is offline
 
Reply With Quote Quick reply to this message
Old 07-29-2007, 12:02 AM   #8
johnsma22
Feedback Score: 0 reviews
 
johnsma22's Avatar
Recipes 
 
Join Date: Jun 2006
Location: Taunton, MA
Posts: 1,890
Liked 32 Times on 26 Posts

Default

I wanted to take a look at this utility, not out of necessity, but curiosity. I already found one of the most useful tools for adjusting water chemistry that is available in a freeware program. It is called Brewater. Check out the link and scroll down a little and download Brewater 3.0. It is quite an amazing little Windows Utility. It has drag sliders that let you make adjustments to ion concentrations without having to type in the numbers. I have used it with much success so far.

I might as well link to the amazing, and simple Mash Water Calculator that I use for batch sparging. It makes it simple to find your strike water volumes, etc. And it's also free! Scroll down to near the bottom and download Mashwater 3.3.

__________________
Cheers,
John

Last edited by johnsma22; 07-29-2007 at 12:06 AM.
johnsma22 is offline
 
Reply With Quote Quick reply to this message
Old 07-29-2007, 01:29 AM   #9
Bearcat Brewmeister
Pour, Drink, Pee, Repeat
HBT_LIFETIMESUPPORTER.png
Feedback Score: 0 reviews
 
Bearcat Brewmeister's Avatar
Recipes 
 
Join Date: Feb 2006
Location: Cincinnati, OH
Posts: 698
Liked 17 Times on 7 Posts

Default

Those of you with the runtime errors, let me know what versions of Excel and Windows you are running. The Excel file should have a macro that runs at opening and loads the Solver AddIn for use in macros and Excel. It is possible that you need the MS Office disk for this. For those of you that manually want to correct this until I can find an automated fix, here are the steps:

-Open the file and get the error, then select debug. This should open bring up a MS Visual Basic window.
-Click the menu Tools > References... and select SOLVER from the list. If it is not on the list, it may need to be found using the Browse... button (the file type drop down box should be changed to .xla). If it is not on your computer, it is probably because you didn't have it selected as part of the install package when you loaded MS Office. You can load it off of your disk, but I will also add a link to the add-in here (not sure if this will work with all Excel vesions, but here it is any way - save it and then follow this step from the beginning, browsing to the place you just saved it).
-Should work now, so exit out of Excel and try again. If it still does not work, in Excel (don't click debug - this fix is not in the Visual Basic window), select the menu Tools > Add-Ins... and select Solver. If it is not on you list, you will have to browse for it as in the step above.
-Should work now. If not, I have no clue and will have to take it to my Excel forum for help. Don't forget to let me know Excel version and Windows version.

Thanks for the help testing. I have these addins in my computer and I tried to have the program load it for those that don't, but since I already have them I couldn't verify it would actually load for you those that didn't have it. Really appreciate the help.

Next on deck is a tool that the user selects a beer style and it will give you the correct temperature range for serving, the CO2 pressure you should use for those temperatures, and options for beer line length at different diameters that will provide a proper pour.

__________________
Kegged: Belgian Dark Strong, Robust Porter, Ryewine, Old Ale
Secondary:
Bock
Primary:
--
Next Up: Baltic Porter, Saison
Projects: Brutus Strut-stand, Freezer Conversion (Done), HERMS (Done), Lager Fermentation Mini-fridge Extension (Done)
Drinking: Great Lakes Edmund Fitzgerald, Fuller's 1845, Lakefront Fixed Gear, New Glarus Moon Man, Tröegs Troegenator
Bearcat Brewmeister is offline
 
Reply With Quote Quick reply to this message
Old 07-29-2007, 02:16 AM   #10
Bearcat Brewmeister
Pour, Drink, Pee, Repeat
HBT_LIFETIMESUPPORTER.png
Feedback Score: 0 reviews
 
Bearcat Brewmeister's Avatar
Recipes 
 
Join Date: Feb 2006
Location: Cincinnati, OH
Posts: 698
Liked 17 Times on 7 Posts

Default

Quote:
Originally Posted by johnsma22
I wanted to take a look at this utility, not out of necessity, but curiosity. I already found one of the most useful tools for adjusting water chemistry that is available in a freeware program. It is called Brewater. Check out the link and scroll down a little and download Brewater 3.0. It is quite an amazing little Windows Utility. It has drag sliders that let you make adjustments to ion concentrations without having to type in the numbers. I have used it with much success so far.
This tool is one that I had seen in the past, but not the new 3.0 version. I like what it does, but wanted to automate the process to minimize the error instead of moving sliders. Now v3.0 of this tool seems to do it. One of the difficult things with water adjustments is that when you put in an additive, it usually affects multiple water parameters at the same time. Getting closer in one parameter sometimes means getting further away in another, and this sounded like an optimization problem that Excel Solver is really good at. The only difference in this tool to mine is that mine optimizes by weighting the hardness and alkalinity ions heavier in the error function as they affect pH. Mine also calculates distilled water to account for situations where you need to reduce ions. It is a pretty hairy math problem when you are too high in some and too low in others.

I do like the option this tool has with gram to teaspoon conversion. I decided to go with teaspoons on additions with no option since many people do not have the capability to weigh things in fractions of a gram. The other thing I like in this tool are the preset target water profiles you can load. I was already planning this for v2.0 of mine by way of a drop down box of cities around the world that you select from and it auto-populates the target water boxes.
The only thing I don't like on this tool is that it lets you enter Ca, Mg, and hardness values. By knowing 2 you should always be able to get the third. By letting you enter all 3 values, you have the opportunity to enter numbers that are mathematically inconsistent with each other. The tool does not error out if you do, so I have no idea how valid the results are in this situation. Mine gives you an error if you try to enter all three values. Same thing goes with bicarbonate and alkalinity. Otherwise, this is a really nice tool. Maybe I can speak with the author and he can incorporate some of my suggestions for his v4.0.


__________________
Kegged: Belgian Dark Strong, Robust Porter, Ryewine, Old Ale
Secondary:
Bock
Primary:
--
Next Up: Baltic Porter, Saison
Projects: Brutus Strut-stand, Freezer Conversion (Done), HERMS (Done), Lager Fermentation Mini-fridge Extension (Done)
Drinking: Great Lakes Edmund Fitzgerald, Fuller's 1845, Lakefront Fixed Gear, New Glarus Moon Man, Tröegs Troegenator
Bearcat Brewmeister is offline
 
Reply With Quote Quick reply to this message
Reply


Quick Reply
Message:
Options
Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
New Water Chemistry Calculator larrybrewer Brewing Software 31 01-12-2011 01:31 AM
Brewer's Friend Water Chemistry Calculator mgo737 All Grain & Partial Mash Brewing 34 07-28-2010 06:50 PM
Mash efficiency and water chemistry question frolickingmonkey All Grain & Partial Mash Brewing 5 03-02-2009 02:42 AM
Water chemistry - target mash or batch? larrybrewer All Grain & Partial Mash Brewing 5 02-06-2009 02:45 PM
Partial Mash Water Chemistry Castawayales All Grain & Partial Mash Brewing 6 03-11-2007 07:52 AM