Author Topic: Utilization of BeerSmith data for Statistics  (Read 2617 times)

Offline EddyB

  • BeerSmith Apprentice Brewer
  • **
  • Posts: 14
  • BeerSmith 2 Rocks!
Utilization of BeerSmith data for Statistics
« on: May 13, 2020, 09:54:28 AM »
The topic of utilizing BeerSmith data for graphs and statistics was already mentioned several times in the forum. I would be glad to see your opinion about the approach I chose which I described in the document attached. I also added the custom report which I wrote.

I have looked at data transfer from BeerSmith to EXCEL, graphs, and some data evaluation.

I will see (and report) how much my forecast for efficiency at different grain weight and/or target gravity for the Braumeister will work out for future brewing?s.

If anyone would have an idea about TAGS for the measured values in the session sheet of the BeerSmith his help would be appreciated! Brad has sent me a link from the forum, but those TAGs are not included. Looking forward for your opinions and thoughts!

Cheers
Eddy
« Last Edit: May 13, 2020, 10:19:14 PM by EddyB »

Offline Oginme

  • BeerSmith Grandmaster Brewer
  • *****
  • Posts: 3115
  • Goats, guitars, and a home brew; Life is good!
    • Longvu LaManchas
Re: Utilization of BeerSmith data for Statistics
« Reply #1 on: May 13, 2020, 11:28:40 AM »
It looks really good.  As a former process control engineer and process modeling engineer (again, former), I can appreciate the design and where you are coming from.  I keep a much more simplified database (well, excel spreadsheet) with a similar bent.  I focus more on the specific equipment profile variables and potential error tracking.  I don't use the targets from BeerSmith so much as the actual measured results.  One thing to be careful of is that the program uses the pre-boil volume as a thermally expanded value (you can track this on your 'vols' tab) in the calculation for pre-boil gravity.  So the gravity target is actually skewed by the thermal expansion coefficient (as is the calculated mash efficiency).  This could throw off your numbers if and when you do a gravity point balance to check your calculations. 

The real beauty in tracking these values is when I make a deliberate change in my process.  I can immediately pinpoint what the results of such a change were if they exceed the natural variability caused by process, measurement or raw materials.  Because I have this background variability (noise) already calculated, I can set up control limits to determine if the process is drifting as well.
Recycle your grains, feed them to a goat!

Offline EddyB

  • BeerSmith Apprentice Brewer
  • **
  • Posts: 14
  • BeerSmith 2 Rocks!
Re: Utilization of BeerSmith data for Statistics
« Reply #2 on: May 13, 2020, 03:24:28 PM »
Thank you! Obviously, we do share a similar business background. It would be interesting to exchange our EXCEL files and calculations. In fact, I am also not so much interested in the targeted values as to find out more about the critical parameters defining the process to improve the accuracy of target values.
 
Basically, one could enter all data- especially the measured values- manually into such a spreadsheet. But from my experience I prefer one set of source data only and this should be the sessions sheet of the BeerSmith. Don?t need to tell you- you find a mistake in one calculation, change it in the spreadsheet but not the session data and one day when you look back you don?t know what?s right and what?s wrong.

Therefore, I would like to know the $TAGS for the measured volumes. And I am positive that if the whole creation process for such a spreadsheet and related analyzes is comfortable enough lots of brewers could draw advantage out of it.

Offline Oginme

  • BeerSmith Grandmaster Brewer
  • *****
  • Posts: 3115
  • Goats, guitars, and a home brew; Life is good!
    • Longvu LaManchas
Re: Utilization of BeerSmith data for Statistics
« Reply #3 on: May 13, 2020, 06:59:38 PM »
Entering the data into a spreadsheet is essentially how I do it.  It is not difficult as there are only a few values to transfer.  Here is a recent sheet with all the brews I have made (at 240v) on my Anvil Foundry system.  It includes the early brews where I was dialing in the system, but it shows the basic calculations.

I take my volume readings at mash out temperatures of 170F (77C) so I make a correction for the volume based upon the thermal expansion at that temperature.  I make corrections to room temperature volumes to perform my gravity point balance, which is how I determine if a reading is funky or how much to depend upon my measurements.  Normally they are very good, since I take the time to develop accurate measuring sticks for volumes and check calibration of my hydrometers/refractometers on a regular basis. 

I also documented my development of an equipment profile for the Anvil Foundry including the first brew (made at 110v) on the equipment topics here on the forum. 
Recycle your grains, feed them to a goat!

Offline EddyB

  • BeerSmith Apprentice Brewer
  • **
  • Posts: 14
  • BeerSmith 2 Rocks!
Re: Utilization of BeerSmith data for Statistics
« Reply #4 on: May 14, 2020, 02:15:26 AM »
Thank you for your illuminating file!

I am ashamed because my file is much simpler than yours, but I attach it anyway. By offering your data you gave me the chance to evaluate the SPC approach by carrying out two simple analyzes for both systems (see doc attached) and it looks like the result makes sense!

Looking forward to your opinion! 

« Last Edit: May 14, 2020, 02:26:42 AM by EddyB »

Offline Oginme

  • BeerSmith Grandmaster Brewer
  • *****
  • Posts: 3115
  • Goats, guitars, and a home brew; Life is good!
    • Longvu LaManchas
Re: Utilization of BeerSmith data for Statistics
« Reply #5 on: May 14, 2020, 09:54:54 AM »
You are carrying a lot more data than my simple file.  I am more focused on the basic equipment profile parameters and a check on the measurements through the gravity point balance before and after the boil. 

I also key more on mash efficiency rather than brew house efficiency.  While I know the BHE is what BeerSmith keys off of for the extraction, I find that it varies greatly based upon my actual practice.  Prior to getting the Anvil, I was doing straight BIAB and throwing my hops into the kettle.  This was reflected in the trub loss correlation to hop loading (boil/whirlpool) and I could model that pretty accurately.  Rather than go through and adjust recipes based upon that, I just set my trub loss going into the fermenter at 1 liter.  This more than covered any losses due to hop or flavoring additions during the wort manufacturing.  I can then transfer as much wort as I want into the fermenter (to the limits of the fermenter capacity and required head space) and measure the trub left in the kettle.

Moving to the Anvil, I had pretty much done the same thing initially.  The recirculating pump really does not like the hop debris much though and I soon graduated to a hop basket for the unit.  I still have not optimized the system for the amount of wort I can recover to minimize trub loss, but aim for the same 11 liters post boil volume.  This is why I run the deviations on the far right to see where any extra volume or losses come from in the process.

Key to this also is the recovery of free wort from the grains following the mash.  I line the Anvil basket with my BIAB bag which allows me to (1) crush a little finer than I could given the drainage holes in the mash basket, (2) compress the bag by lifting it out and then squeezing to get as much of the free wort out as I can, and (3) clean up a lot faster.  This easily bumps my mash/lauter efficiency by 4% to 5% at a minimum and one of the things that levels out my efficiency vs grist weight correlation.

I will also calculate conversion efficiency if I think there is an issue, such as with the last entry brewed on 3/29/20.  Horrible efficiency for some reason and my calculation of conversion efficiency was way down.  Normally I am 97% to 99% in conversion efficiency, which given the potential variation in moisture in the grains is not too bad.  That brew was just over 90%, so something happened with my mash that I have yet to nail down.  That was my last standard batch and I have been brewing some test batches since (back to stove top BIAB, 3 liter batches) without issue, so that rules out the grind.  Could be I messed up weighing the grains out, who knows at this point.




Recycle your grains, feed them to a goat!

Offline EddyB

  • BeerSmith Apprentice Brewer
  • **
  • Posts: 14
  • BeerSmith 2 Rocks!
Re: Utilization of BeerSmith data for Statistics
« Reply #6 on: May 15, 2020, 04:21:52 AM »
Both of our all in one system share some equal characteristics. I think it allows some exchange of experience. I just watched a video about the ANVIL Foundry to understand the system. Some thoughts:

-   I do not like the idea of squeezing the tube for adjusting the flow. If you zoom into the picture of my brewery setting you will find a little fuse box. I installed a cheap but effective frequency controller (you find lots of them online) to control the speed of the pump. It works perfect!

-   I do sparging in a way I could imagine it will also work for the Foundry: again, in my setting you will find a simple pulley from the home depot. I pull out the grain tube (basket) just enough that the room underneath the false bottom is still full of wort without air voids. At this height I start recirculating wort with the pump for rinsing and filtering. If I did just hit the target pre-boil gravity, that is it. If the pre-boil gravity is high enough, I do sparging with preheated water until I reach the target gravity. This has improved my mash efficiency a lot! Towards the end of sparging I pull out the mash basket well over the wort level and start the boil while the rest of wort is dropping out from the grainbed. As I do know the weight of the empty basket and the grain- weight, a spring scale between the hook of the pulley and the basket (also to be seen in my setting) helps me to evaluate how much water is contained in the grainbed.

-   The installation of the inline filter from Utah Biodiesel Supply has reduced my trub loss a lot which was proven by experience and shown in the statistic. I use the 300-micro sieve. For this process, the electronic speed regulation of the pump is essential. You might run with a lower speed at the beginning and with more load in the filter you gradually increase the speed. Utah Biodiesel Supply also offers lots of hop baskets and other useful stuff for homebrewing.

Offline Oginme

  • BeerSmith Grandmaster Brewer
  • *****
  • Posts: 3115
  • Goats, guitars, and a home brew; Life is good!
    • Longvu LaManchas
Re: Utilization of BeerSmith data for Statistics
« Reply #7 on: May 15, 2020, 06:55:13 AM »
Both of our all in one system share some equal characteristics. I think it allows some exchange of experience. I just watched a video about the ANVIL Foundry to understand the system. Some thoughts:

-   I do not like the idea of squeezing the tube for adjusting the flow. If you zoom into the picture of my brewery setting you will find a little fuse box. I installed a cheap but effective frequency controller (you find lots of them online) to control the speed of the pump. It works perfect!

-   I do sparging in a way I could imagine it will also work for the Foundry: again, in my setting you will find a simple pulley from the home depot. I pull out the grain tube (basket) just enough that the room underneath the false bottom is still full of wort without air voids. At this height I start recirculating wort with the pump for rinsing and filtering. If I did just hit the target pre-boil gravity, that is it. If the pre-boil gravity is high enough, I do sparging with preheated water until I reach the target gravity. This has improved my mash efficiency a lot! Towards the end of sparging I pull out the mash basket well over the wort level and start the boil while the rest of wort is dropping out from the grainbed. As I do know the weight of the empty basket and the grain- weight, a spring scale between the hook of the pulley and the basket (also to be seen in my setting) helps me to evaluate how much water is contained in the grainbed.

-   The installation of the inline filter from Utah Biodiesel Supply has reduced my trub loss a lot which was proven by experience and shown in the statistic. I use the 300-micro sieve. For this process, the electronic speed regulation of the pump is essential. You might run with a lower speed at the beginning and with more load in the filter you gradually increase the speed. Utah Biodiesel Supply also offers lots of hop baskets and other useful stuff for homebrewing.

Having previously worked in the paper industry for 23 years, the throttling of a pump output is a very common way to achieve flow control.  The clamp that comes with the Anvil is an inexpensive way to accomplish this.  I have decided that I want to replace this with a hand valve (ten turn with markings) to regulate the flow.  I considered a rheostat to adjust the pump speed, but that also creates an issue of then protecting the rheostat from accidental spills and potential high humidity operation.  More money than I want to invest right now and it cuts down on the system portability, which is a big plus for my situation.

Once I started using my Anvil and achieved the grind quality which gave me a good consistent mash efficiency, I tried sparge vs no sparge and the difference was not outside my current variability of mash efficiency results.  Plus, again a brewing style choice, it suits me to add all the water in up front for most of the brews.  I then have the time of the mash to do other things.  I have a farm, so feeding, milking, watering, tending my goats is a fixed schedule which I must manage my brew day around.  Simplicity is one of the reasons I chose to go the route of an all-in-one system.  That and the desire to get off the stove top during the winter months.

I recirculate through my hop basket when heating up from the mash to boil.  Then at the end of the boil, I recirculate again through the hop basket.  Having lined the mash basket with a bag during the mash, there is not a lot of debris in the kettle.  The trub loss I get now is mostly a limitation on the drainage of the device, plus some protein which is precipitated from the wort during the cold break. 

When I get to making a larger batch requiring a sparge, I will see if I can replicate your sparge method.  I like your idea of keeping the grain bed just at the top level of the wort in the kettle to create more of a plug flow of water through the grains. 

One question on your use of the basket weight for figuring out the water retention.  How do you account for the extract from the grains?  I have considered this approach, but my calculations are consistent enough to account update BeerSmith and get reproducible, reliable targets from the model.

Recycle your grains, feed them to a goat!

Offline EddyB

  • BeerSmith Apprentice Brewer
  • **
  • Posts: 14
  • BeerSmith 2 Rocks!
Re: Utilization of BeerSmith data for Statistics
« Reply #8 on: May 15, 2020, 09:16:34 AM »
Agree- I used a squeeze valve for Gypsum powder in a pneumatic feed to a plant where paper played an important role, too (so much about our common business background). The costs of the little fellow I installed (see picture attached) are 28$ but an installation like I did is killing the issue of portability- you are right.

Regarding rinsing/ sparging I got your point. I assume- and I have seen it from the correlation- that the Foundry has a less critical grainbed than the Braumeister. In the BM mash basket, the pressure comes from the bottom and squeezes the grain bed upward to the top sieve. And believe me- this is massive pressure! I know because one time I forgot to switch off the pump before opening the top sieve for some stirring and up came the grain bed like a sausage! And you are right, in some cases the increase in efficiency is not dramatic so I just do it for the sake of filtering through the grain bed (in which case I use my three-way valve to bypass the inline filter).

Appreciate your idea about filtering through the hop basket!

Regarding the usage of the digital weigh scale: I found the calculated grain absorption from the BeerSmith plus the net weight of the equipment a good approximation. And I am looking at the change rate of the reading. The little amount of volume involved does not justify big science. As an approximate tool it is doing a good job given the cost of some 20$ for a simple scale.

But everything we discussed in those last two posts concerns on subject: all in one system are nice if you need portability- but you could do a lot more when installing it at a fixed place. Before I refurbished my outbuilding, I did have the same limitation. Nowadays- with the electronic removed from underneath the kettle (more safety, easier cleaning of the kettle, more comfortable adjustment and readability), the pulley, the three-way valve and the inline filter- brewing has become more fun, it is neater and more efficient. Looks like the ANVIL Foundry offers a lot of flexibility for set ups like this! I do like the fact that the heating element is underneath the bottom plate which allows easy cleaning.
« Last Edit: May 16, 2020, 12:52:32 AM by EddyB »

Offline Oginme

  • BeerSmith Grandmaster Brewer
  • *****
  • Posts: 3115
  • Goats, guitars, and a home brew; Life is good!
    • Longvu LaManchas
Re: Utilization of BeerSmith data for Statistics
« Reply #9 on: May 16, 2020, 08:21:54 AM »
One of the things that I would like to see in the software is the automatic calculation and comparison of those key settings for grain absorption and boil off rate.  All of the needed information is there in the software to spit out these metrics with the exception of a user entry field for actual water used.  While it was listed on my wish list, it cannot be mentioned nearly enough as an aid for those users who may not want to go through the calculations themselves and would make the software much stronger.
Recycle your grains, feed them to a goat!

Offline EddyB

  • BeerSmith Apprentice Brewer
  • **
  • Posts: 14
  • BeerSmith 2 Rocks!
Re: Utilization of BeerSmith data for Statistics
« Reply #10 on: May 16, 2020, 11:29:20 AM »
Great idea! This value would be the final confirmation for all volume related calculations. It would definitively help to identify input errors, too (or reveal a mess in the process). Is this topic on the suggestion list already?

Offline Oginme

  • BeerSmith Grandmaster Brewer
  • *****
  • Posts: 3115
  • Goats, guitars, and a home brew; Life is good!
    • Longvu LaManchas
Re: Utilization of BeerSmith data for Statistics
« Reply #11 on: May 16, 2020, 11:39:01 AM »
I put it on my wish list for the next update of BeerSmith which is in the 'Suggestions' thread. 
Recycle your grains, feed them to a goat!

 

modification