Dismiss Notice
Extreme Beer Fest:Los Angeles

Join us December 9th in Los Angeles for the ultimate throwdown of craft beer creativity!

Learn more...
Dismiss Notice
Introducing: Project Extreme Brewing (a Dogfish Head + BeerAdvocate project)

BrewCipher 3.41

Discussion in 'Homebrewing' started by VikeMan, Jul 10, 2014.

Thread Status:
Not open for further replies.
  1. VikeMan

    VikeMan Meyvn (1,335) Jul 12, 2009 Pennsylvania
    Beer Trader

    BrewCipher 3.41 spreadsheet is now up for download. This is a very minor update.

    New in Version 3.41:
    - Enhanced the display of water volumes on the Recipe Tab. If you are using a Hot Liquor Tank with a Deadspace, the Recipe tab will now (in one place) show both the 'net' water required and how much you'll put in your HLT (including applicable Deadspace)
    - Added some ingredients (mostly Hops and a few grains)


    Feature Recap...
    - Predicts attenuation based not only on yeast strain, but also on mash conditions and grain bill composition
    - Understands that simple sugars are 100% fermentable, and that they are not subject to mash efficiency or yeast strain factors
    - Understands that some gravity contributors are not fermentable at all
    - Accounts explicitly for wort and water losses, and adjusts total water (and related calculations) accordingly
    - Uses a correct Tinseth formula with Avg Boil Gravity in the 'bigness' factor rather than the pre-boil OG (which is both popular and wrong)
    - Incorporates optional Modified Tinseth Formula…
    - Limits IBUs to 110 (solubility limit)
    - Splices new utilization curve to Tinseth utilization curve above 65 IBUs, to more closely agree with actual vs theoretical IBUs measured
    - Adds bitterness contributions for post boil hop additions
    - Hop Utilization Multiplier parameter to fine tune hop utilization to your system​
    - Single infusion batches with single batch sparge, or mashout and lauter, or Brew-In-A-Bag
    - Wort Oxygenation Recommendations
    - Lagering Days Recommendations
    - Highlights any Gravities, ABVs, IBUs, and SRMs that are not within BJCP style guidlelines
    - Computes Diastatic Power for the Mash, and warns when grist's DP is marginal or poor
    - Scales Grain Bill to any desired Original Gravity
    - Calculates Mash pH and results of acid and Brewing Salt Additions, including Sparge Water acidification
    - User Storable Water Profile Input (feature works in Excel, but not Apache Open Office)
    - Predicts Mash Efficiency based on known Efficiency and change in grain weight and/or sparge to no-sparge or vice versa
    - Automatically computes Yeast Starter Volumes, including stepped starters. Supports harvested yeast slurries.
    - Includes an optional, improved ABV calculation
    - Inputs for Post Boil/Post Fermentation gravity and volume data, to compute actual mash efficiency, attenuation, and ABV
    - Converts Refractometer readings Specific Gravity Values
    - Choice of U.S. standard measurements or Metric
    - Choice of Plato or SG gravity measurements
    - More user selectable Brewhouse Parameters than you can shake a stick at (but the defaults will work pretty well for most people too)

    BrewCipher V3.41
     
    Thorpe429, Wanda, ChrisMyhre and 5 others like this.
  2. jae

    jae Initiate (190) Feb 21, 2010 Washington
    Beer Trader

  3. VikeMan

    VikeMan Meyvn (1,335) Jul 12, 2009 Pennsylvania
    Beer Trader

    Excel.
     
    CurtFromHershey likes this.
  4. jae

    jae Initiate (190) Feb 21, 2010 Washington
    Beer Trader

    No, "word?" like "for real?". You're from western PA, huh?
     
  5. VikeMan

    VikeMan Meyvn (1,335) Jul 12, 2009 Pennsylvania
    Beer Trader

    [/sarcasm] I know. And no.
     
  6. wspscott

    wspscott Savant (979) May 25, 2006 Kentucky
    Subscriber

    Thanks again for your work on this and for sharing.

    One request, can the brewday print sheet show the actual water results after any salts? Right now, it just shows what is added, but doesn't say what the final result of the additions are. Or maybe something with % distilled for the mash and sparge. In the end, this wouldn't matter if I could always have enough distilled water on hand, but that requires a lot more foresight than I am capable of :slight_smile:
     
  7. cmmcdonn

    cmmcdonn Initiate (0) Jun 21, 2009 Virginia

    I'm a tech nerd so I took a look at the hidden sheets to see the "back end" of it all and it makes BrewCipher look even more impressive. +100 internets

    I may make the switch from Beersmith. I can't get over their Extract/Partial mash error (error to me anyway).
     
  8. VikeMan

    VikeMan Meyvn (1,335) Jul 12, 2009 Pennsylvania
    Beer Trader

    I'll take a look at that, at least the % distilled part. Maybe the final water results too if they'll fit. Will have to make the displays of water tab info on the BrewDay sheet optional, since not everyone uses the water tab.
     
  9. VikeMan

    VikeMan Meyvn (1,335) Jul 12, 2009 Pennsylvania
    Beer Trader

    If you could actually see those sheets (and not just the list of them), you are indeed a tech nerd.

    I started making BrewCipher because of shortcomings in some other software/websites. Plus, I like being my own help desk. I get much faster response that way.
     
    ChrisMyhre, jbakajust1 and cmmcdonn like this.
  10. cmmcdonn

    cmmcdonn Initiate (0) Jun 21, 2009 Virginia

    I'm a computer forensics guy and Excel sheet protection is extremely weak.

    It actually gave me some afternoon reading material. I'll be checking out the hombrewing physics blog you linked to (mash_chem) during my down time. :slight_smile:
     
  11. atomeyes

    atomeyes Disciple (383) Jul 13, 2011 Ontario (Canada)
    Beer Trader

    yeah, i am scared to leave BeerSmith just because I've finally tweaked it to where it should kind of sort of be. and i also have a mac w/o MS Office, so i can't use this spreadsheet.

    fuck, do i need to buy a laptop just to brew?
     
  12. jbakajust1

    jbakajust1 Moderator (1,086) Aug 25, 2009 Oregon
    Subscriber Beer Trader

    I think you can use it on Google Drive too?
     
  13. VikeMan

    VikeMan Meyvn (1,335) Jul 12, 2009 Pennsylvania
    Beer Trader

    Google drive doesn't support some of what the sheet does, so I don't publish it in google docs format.
     
    atomeyes and jbakajust1 like this.
  14. Wanda

    Wanda Initiate (159) Nov 23, 2006 North Carolina

    If you have iWork you should be able to download it from Drive, then drag it to Numbers. I should open it from there.

    Thanks for the work VikeMan. Learning to homebrew has been a lot more in depth than I would have imagined but it's a helluvalota fun!
     
    atomeyes likes this.
  15. Thorpe429

    Thorpe429 Poo-Bah (5,387) Aug 18, 2008 Illinois
    Industry Beer Trader

    Amazing as always. I've now fully converted over from BeerSmith. Just need to set up a separate inventory in another Excel file. I always liked the idea of having that in BeerSmith, even though I never actually used it :slight_smile:

    Thanks again as well for taking in so many water suggestions. One thing that would be amazing (though I have no idea how complex this would be for you and/or Utahbeerdude) would be to make it possible to calculate pH changes for adding lactic acid pre-boil and post-boil based on a "current pH" input so that adjustments could be made there as well.

    Cheers!
     
  16. utahbeerdude

    utahbeerdude Disciple (376) May 2, 2006 Utah

    This is an interesting idea, and it is something that could in principle be done, if one knew the nature of the buffers in wort or there were some data out there that directly measured pH changes to wort upon acid addition. On the other hand, I believe that if one hits the correct mash pH, then pH downstream tends to take care of itself pretty well.
     
  17. Thorpe429

    Thorpe429 Poo-Bah (5,387) Aug 18, 2008 Illinois
    Industry Beer Trader

    Thanks for the response. Thinking about several different scenarios, the most basic of which could be if someone were doing a few day "sour wort" and wanted to get initial pH down to X (4.5 maybe?) to avoid any potential nasties. I would need to look back at notes/things I've saved, but I've certainly come across a few things mentioning getting boil pH or post-boil pH down to X to achieve X, and wanted to check whether that was something all that difficult to determine.
     
  18. jivex5k

    jivex5k Initiate (0) Apr 13, 2011 Florida

    So I downloaded this just now...Wow man! That's some serious work you put in. Thanks for sharing this with the community.
     
  19. jmich24

    jmich24 Devotee (447) Jan 28, 2010 Michigan

    Another suggestion, it would be nice to have a space to enter length(days) and temp of the dryhop additions.
    Example: Citra, Pellet, 1 oz, 5 days, 65 degrees.
     
  20. scurvy311

    scurvy311 Disciple (346) Dec 3, 2005 Louisiana

    Used BC side by side with ibrewmaster for recipe/predictions and EZ water for water/pH calcs. BC beats ibrewmaster in terms of more accurately predicting OG/FG and ingredient contributions. I find it is more accurate at predicting pH than ezwater, at least my last 2 batches where I took readings.
     
  21. VikeMan

    VikeMan Meyvn (1,335) Jul 12, 2009 Pennsylvania
    Beer Trader

    Another Minor Update, not worth a new thread...
    Version 3.42 is up. New for V3.42, based on user requests...

    - Added more Water Tab results to the Recipe and BrewDay tabs. (User selectable, since some people might not use the water tab, and the results therefore wouldn't be relevant.)
    - Added style parameters for (and added to style dropdown) Sour styles. Note that I have not added souring bugs to the yeast selections, because there's no attenuation data available for them, and I very strongly suspect they wouldn't fit the fermentability models. I can't in good conscience just make them up. But users can certainly add strains to their hearts' content.
    - Added more informational fields (location, days, temp, method) to the Dry Hop section
    - Added BU:GU output

    Google Drive/Docs is acting flaky again in folder view. If you can't download from the link above, these links will take you directly to each file...

    .xls (for excel users): https://drive.google.com/file/d/0B-9BMe0xDqppX21TUC1fMkltT1U/edit?usp=sharing
    .xlsm (for Apache OO users): https://drive.google.com/file/d/0B-9BMe0xDqppZE41eU53MHlXb2s/edit?usp=sharing

    If downloading from the individual file links, use the "File" menu on the linked page. Google Docs is free and sometimes I get what I pay for.
     
    #21 VikeMan, Jul 26, 2014
    Last edited: Jul 26, 2014
  22. Thorpe429

    Thorpe429 Poo-Bah (5,387) Aug 18, 2008 Illinois
    Industry Beer Trader

    One thing I noticed is that on the water tab, it has "Kettle Amount (g)" for the additions, but then speaks of "Sparge H20 Concentration (ppm)" in the resulting water area. Should the latter be changed to something like "Kettle/Boil" amount?

    It seems like it shouldn't be linked to the sparge water, as the sparge water acidification recommendations for acid doesn't change based on what's input into the "Kettle Amount" salt additions now. It should, as putting 100g of extra calcium (whether from CaCl or CaSO4) should change the pH of the sparge water, thus changing the acidification recommendation, right? I don't know how difficult it would be to link sparge/kettle additions and the sparge acidification area, but changing terminology so that salts are only added to the mash and/or boil kettle and acid is the sole thing for acidifying sparge water would make everything consistent.

    Thanks again for all the work, love having all of this information together instead of having 10 different calculators/programs open :slight_smile:

    Finally, as a request list for next time (unless I'm just missing this), an efficiency calculator would be awesome!
     
  23. VikeMan

    VikeMan Meyvn (1,335) Jul 12, 2009 Pennsylvania
    Beer Trader

    If you read the comment attached to the "Sparge H20 Concentration" cell on the Water Tab, it explains that 'sparge water' salts are normally added to the kettle, and that the sparge water acidification calculation assumes that they will be (added to the kettle). The problem with potentially labeling it "Kettle H20 Concentration" is that some people would assume that meant a combined concentration (for all the water that went into the kettle, i.e. mash plus sparge). That's why it's labeled "Sparge H20 Concentration)," but with the embedded comment to explain. (BTW, adding CaCl or CaSO4 to sparge water itself wouldn't change the pH of the sparge water. The reason they change the pH of a mash is that the Ca ions react with phosphates from the malt, releasing H+ (protons).)


    I'm not sure what you mean by efficiency calculator, but the Recipe tab has a place to input your actual post boil OG and volume, calculating actual mash efficiency. There's also a tab labeled "Mash Efficiency Predictor" which you can use to predict the change to your mash efficiency by switching from sparge to no-sparge (or vice versa), and/or chaning the size of the grain bill.
     
  24. Thorpe429

    Thorpe429 Poo-Bah (5,387) Aug 18, 2008 Illinois
    Industry Beer Trader


    Thanks all around. Makes sense on the water calculations, and good point on pH, always forget that the calcium is reacting with something to lower the pH :slight_smile: Missing the note under "Sparge H20 Concentration" serves me right for trying to make comments while in the middle of brewing and transferring!

    Missed the OG/volume entry on the recipe tab, and will start using that.

    Thanks as always!
     
  25. jakwi

    jakwi Initiate (0) May 14, 2014

    Hey Vikeman,

    So I have been using your sheet for a number of batches now. Overall I think it is great, it is my Go To sheet. I have a couple of comments. The first comment and I hope that you'll take it in the spirit it is given. For my taste the yellow is to glaring. The thing is that I often prepare my recipe during the week for the weekend, and the yellow doesn't look like any of the other sheets I might use at work so it stands out. Any other more muted color would be great, but that is just my opinion. I'd change it myself, but of course the sheet is protected.

    Another change that I think it would be helpful to add the following info to the Brewday printme sheet, just sort of as a reference. ( I often write some of this info on the sheet after I print it. )

    23.25 lbs Total Grain Bill
    Boil off Rate
    1.5 gal/hour
    Total Preboil water volume
    15.52 gallons
    Total Post boil volume
    10.25 gallons
    Total volume to boil off
    5.27 gallons
    Calculated boil time
    3.52 hours

    My last comment has to do with Water calculations. I often use the calculator over at brewers friend, http://www.brewersfriend.com/water-chemistry/, to help me determine what to add. At the bottom it indicates what the flavor profile will be. Since I don't really know what I'm doing this is immensely helpful. I'm not sure how difficult it would be to incorporate something like that, but it would eliminate the extra step of going to a website to work out the salt additions. Also if there were a way to calculate suggested water additions to meet a profile that would be cool to.

    Of course these are just ideas that I think would be helpful, but I'm pretty new to this so it may be info that no one else is interested in seeing. Thanks again for your work on this, even if you don't change anything else it is a great sheet.


     
  26. VikeMan

    VikeMan Meyvn (1,335) Jul 12, 2009 Pennsylvania
    Beer Trader

    Yellow background is (or at least was at one time) sort of a defacto standard for input cells in spreadsheet models. I could of course change it to any color, but what? Suggestions welcome...if enough people want a different color (and the same color as each other), I'll be happy to do it.

    I could squeeze in some summary data like the above...it would have to be right beside the grain bill. What do you mean by "calculated boil time?" (The boil time is already displayed on the BrewDay sheet.) Any other users of the BrewDay Sheet mind if I add some summary info beside the grain bill?

    I don't mind suggestions at all. I welcome them. Usually if I can add something of general interest without making the interface more difficult/unwieldy for others, I'm glad to do it.
     
    ChrisMyhre likes this.
  27. ChrisMyhre

    ChrisMyhre Devotee (464) Sep 15, 2013 Massachusetts
    Beer Trader

    Additional summary data on the brewsheet would be at least fine and possibly helpful. I don't mind the yellow entry cells and did also think that was kind of standard, but also would be fine with a change I suppose. I do use Libreoffice so I haven't been able to make much use of the water profiles.
     
  28. jakwi

    jakwi Initiate (0) May 14, 2014

    As far as boil time: The boil time shown on the sheet is taken from the time entered from the recipe sheet, which I assume has more to do with hop additions than volume reduction. What I was looking for is calculated based on the boil off rate and the total amount on pre-boil wort. So if the collected wort is 15.5 gallons and the into the fermenter volume should be 11 gallons then you need to boil off 4 gallons at say 1.5 gallons per hour, so the boil time would be 2.67 hours. Maybe there is a better term for it.

    Or am I misunderstanding how a brew day should go? I consistently have to boil for 2 or 3 hours to get my volume where I need it. It is always sort of a guessing game on timing my hop additions work with the volume reduction.

    As far as the color I vote for a muted grey, but if that is just my preference then no worries, it may very well be the standard, I'm just not familiar with it.
     
  29. VikeMan

    VikeMan Meyvn (1,335) Jul 12, 2009 Pennsylvania
    Beer Trader

    The boil time you enter on the recipe tab is the total boil time (nothing to do with hop boil times directly), and that's what's also printed on the BrewDay sheet. If you are boiling for over 2 and a half hours, you're using way too much water, unless you have some other reason for wanting to boil that long. If you enter the boil time you want (60 minutes or 90 minutes is pretty common), and get all your parameters on the BrewHouse sheet set up properly (boiloff rate, various deadspaces, etc.), BrewCipher will calculate the correct amount of water to use.

    Get your parameters dialed in and you won't have to guess at all. Feel free to send me mail if you need any parameter help.
     
    jakwi likes this.
Thread Status:
Not open for further replies.
  • About Us

    Founded in Boston in 1996, BeerAdvocate (BA) is your go-to resource for beer powered by an independent community of enthusiasts and professionals dedicated to supporting and promoting better beer.

    Learn More
  • Our Community

    Comprised of consumers and industry professionals, many of whom started as members of this site, our community is one of the oldest, largest, and most respected beer communities online.
  • Our Events

    Since 2003 we've hosted over 60 world-class beer festivals to bring awareness to independent brewers and educate attendees.
  • Our Magazine

    Support uncompromising beer advocacy and award-winning, independent journalism with a print subscription to BeerAdvocate magazine.