Water Profiler Optimizer

[Chem Brewer]

Hello!

I've downloaded the Beer Smith trial and I must say, I like it a lot.

One thing that I think would help would be an optimizer for the water profiler tool. Essentially, after selecting a base profile and a target profile, an optimization script could be run in which it searches for combinations (within say 0.25g increments) of additives in order to find the lowest sum of the differences (in absolute value). Essentially... haha.

Does anything like this exist? How do most people match water profiles, by hand? I realize it only takes a few minutes per profile to do it by hand, but I like automation.

[Chem Brewer]

Oops, I just read an old post about a "Calculate" button asking the exact same thing. Sorry for the double post!

Now I admit I don't have much experience in programming, but is it possible to write a script that chooses a random additive, adds 0.25g, then calculates the difference total and compares it to the previous difference total. If it's lower, it keeps the 0.25g, then chooses a different additive. If it's larger, it removes that 0.25g and chooses another additive. If it fails to find a lower difference after x amount of tries, it calls it good.

[MaltLicker]

Strictly my opinion here, but there is so much "free space" in water profiling that this optimization would be tough to execute.  For ex, I have very low mineral water, so I really just try to bump up the minerals to the recommended brewing range for each, as much for yeast health as to hit a certain profile.  Other people may be trying to optimize hops by pushing the sulfate up.  And everywhere in between.

[Chem Brewer]

Oh, I just meant optimize as in optimizing how much of which salts to add to match your personal water supply to a particular region or brewery. Yeah, trying to add in experimental factors, yeesh!

For what it's worth, I was able to accomplish this in Excel using the Solve add-in. I made a table similar to the Beer Smith profiler. I adjusted the "differences" to be absolute values, and summed up the absolute values to give me my optimizable value. I then told the Solve add-in to optimize the amount of all 6 salts to get the minimum sum of differences. You can even add constraints, such as telling it to only look at 0-10g of each salt and only the integers. If I want to add carbonates afterwards for example, I'll just play in the pre-optimized table a bit.

Beer Smith is great and I'm sure I'll be subscribing to it. I love all the internal calculations and ease of recipe storage. I started a paper journal when I was extract brewing... yeah... I'm terrible at writing in it.

[MaltLicker]

Could you share your Excel sheet?  Does it also calculate and try to match the residual alkalinity to a target?  Thx.

[BeerSmith]

Thanks,
  I was looking at a proper simplex algorithm to minimze the variance, but have not implemented it due to the complexity.  Perhaps a simpler approach would be OK here since most of the ions have only two terms in the equation.

  I would be interested in the spreadsheet as well.

Brad

[Chem Brewer]

Here's my excel sheet. I tried to make it self explanatory. Remember that you need the Solve Add-In which you can download through Excel and you also have to have macros turned on.

I did include an alkalinity and hardness value which you can constrain in the Solve macro. Also, I've created the table based on 1 gram units. You can convert it to anything you like: ounces, 0.5 grams, etc.

[dhaenerbrewer]

That is an interesting spreadsheet. Though hard to use. It's easier just to adjust the amounts in beersmith to suit your needs. I'll see about throwing together an application that will do exactly what you have done with this spreadsheet, just with a much greater ease of use.

EG - You input your starting water ppm values and target water ppm values and BAM!, it does all the rest of the work for you. Give me a week or so to see what I can come up with.

Brad - Any input? I'll probably just make a simple app with VB, utilizing a Access DB. I'll shoot you over the code when I'm done.

Darin

[BeerSmith]

Darin,
  I would prefer C++ in some kind of simple function - All of my code is C++ now.

Brad

[Chem Brewer]

dhaenerbrewer, I'm confused, because that is exactly what my spreadsheet does, albeit through Excel. Input your water supply's values, change the target values, and Solve. Again I admit I don't know enough about VB or C++ to do it, but I don't see how the spreadsheet is hard to use. The "results" sections are extra that I copy and pasted to show examples of what it can do. Would you like the basic spreadsheet version without those extra examples? The Solve add-in does all the work.

[Chem Brewer]

Ok, so I tried to create a Macro and Button, and I was getting close, but I've had one too many homebrews, and I don't really care anymore. Solver really is very easy to use. I'm attaching the spreadsheet sans examples, so feel free to use this. Again, this spreadsheet is extremely versatile: you can input any initial water conditions and input any target water conditions. Through Solver you can add any constraints such as integer units (my default), maximum amounts to add (10g my default) any target ion concentration, target alkalinity, and/or target hardness. Again, these extra constraints are totally for your own use. I've set it up so you don't have to fiddle with Solver at all.

In this new spreadsheet all you have to do is input values into the Bold Yellow boxes (your water supply and the target water). Assuming you have Solver installed and Macros enable, click on Data, then Solver. In the pop up box click Solve, let it think for a few seconds, then click ok. You now have the optimized results! The light blue cells indicate the cells used by Solver, but you shouldn't need to worry about these as the solver macro is pre-configured.

If you're having problems using Solver or would like to learn more about it, try this link: http://office.microsoft.com/en-us/excel/HA102190021033.aspx

[dhaenerbrewer]

Ok, I obviuosly didn't know how to use the solver properly. I was solving for each individual mineral, and it took forever to get the results. I wasn't simply solving for the sum. It is in fact extremely easy. Thanks for the good work! I no doubt will be using this frequently. Do you know if there is a way to integrate this type of add-in to a access database?

Brad - I don't think I will be making an app after all, as it works much better than I originally thought. I just didn't know what I was doing. I think maybe I had one too many homebrews myself. I would however would like to discuss with you the possibility of making a version of beersmith that is truly for the professional brewer. Including many more options that are not included with the current version of beersmith. I think you would have a serious market out there. I'm trying to create an access database right now that would be similair to what I am envisioning.

Darin

[big78badger]

Actually this kind of thing can be done with a technique called least square fit optimization.  The key would be to choose the right optimization objective.  I would think a good objective might be the to minimize the sum of the absolute values of the ppm differences between actual and target.  The algomithm could rather easily to programmed to calculate the mineral additions and water dilutions necessary. 

[BeerSmith]

Anyone have the code in C or C++?  I would be happy to roll it in...

Brad