Kit And Extract Beer Spreadsheet

[Edd-UK]

Hi

If you open the spreadsheet. Hit ALT & F11 should bring up visual basic.

In the visual basic Window go to the Tools Menu and select References I have ticked

Visual Basic for Applications
MS Excel 12.0 Object Library
OLE Automation
MS Office 12.0 Object Library
MS Forms 2.0 Object Library
MS Word 12.0 Object Library

I am using Excel 2007 so if using a different version will get a number other than 12.0, hope that fixes the problem.

cheers

Ian

Many thanks

Tried this at first on a saved copy with a recipe I made up yesterday and it failed, said missing ddl.

So tried it on a fresh untouched downloaded copy and it worked perfectly.

Im using Excel 2002

cheers

Edd

 

[Pennywise]

Does this spreadsheet work in Word Starter as that's what comes with my new Lappy, or will I need to get the full Microsoft Word? Sorry if this has been covered, I've searched and read a few pages but haven't find anything covering this.

 

[DU99]

It needs excel there spreadsheets,if you don't have it suggest trying openoffice it's free

 

[Pennywise]

Ahh my bad I meant excel starter, will give open office a crack regardless. Cheers DU99

 

[Mutton Chops]

Without trawling back through the previous 20 pages, could anyone enlighten me to a quick and easy way of transferring recipes from an older version to this latest one?

 

[ianh]

hi Mutton Chops

How to do it is in the Notes worksheet of the spreadsheet.

You copy your old recipe worksheet across and it has more than 10 recipes then you need to insert 4 rows between each set of 10 recipes at row 20,40 etc (start highest row first)
if you have more than 18 recipes you need to cut and paste data in columns A & B so there are no gaps.

cheers

Ian

 

[Mutton Chops]

hi Mutton Chops

How to do it is in the Notes worksheet of the spreadsheet.

You copy your old recipe worksheet across and it has more than 10 recipes then you need to insert 4 rows between each set of 10 recipes at row 20,40 etc (start highest row first)
if you have more than 18 recipes you need to cut and paste data in columns A & B so there are no gaps.

cheers

Ian

Thanks Ian, once again, nice work!

 

[adamh]

I worked out how to use this in OpenOffice on the PC in case anybody uses that.

First you need to enable Macros which OO diables by default - Tools-> Options -> Expand the OpenOffice.org section and click on "Security" then click on the "Macros" button and choose your desired setting.
Second, in the same "Options" window expand the "Load/Save" section and click on "VBA properties". Check the "Executable code" box under Microsoft Excel 97/2000/XP.

Doesn't like opening a save recipe though, so maybe just save a different filename.

 

[kario]

Just discovered this.....AWESOME-NESSS!!!

kudos to Ian.

 

[ianh]

Just discovered this.....AWESOME-NESSS!!!

kudos to Ian.

Thanks and when you progress to BIAB there is a spreadsheet for that also.

cheers

Ian

 

[Mags]

I have tried to get your spreadsheet going on open office on my macbook but it doesn't seem to want to work. I have enabled macros and such but it just doesn't seem to be functional. I'll have another play around with it tonight, probably just being ********.

If anyone has any tips that would be great!

 

[charlie_b]

Hey guys, I'm new to all this and have just chucked on my second batch of Brewcrafts Little Creatures Pale Ale.

With the spreadsheet I've put in

Volume: 21 L
KIT: Black Rock Pilsner Blonde 1.25 kg
LME: -
DME: Wheat dry malt .15 kg
GRAINS/ADJ: Dextrose 1.0 kg (actually brew booster #15)
HOPS: Cascade 15g
Willamette 15g (boiled together for 2 mins in about 4 ltrs with the wort - these were in pellet form)


Spreadsheet gives me an OG of 1.038 and I got one of 1.044. Is the difference because I entered dextrose when I've used Brew Booster #15 (couldn't work out how else to deal with that) and what is the difference between KIT and LME?

Yeast provided doesn't seem to have a code on it to match up with the list. Nottingham Ale Yeast.... even the datasheet on their webpage give nothing! They seem to be happy little bugs tough as they kicked into gear almost as I finished attaching the lid!

Cheers,

Charlie

 

[pete6]

I did the exact same kit a couple of months back, and my OG was also 1.044 - the kit suggest a target OG of 1.048. I put mine down to the fact that i topped up to 22.5L instead of 21L

i read somewhere the Brew booster #15 is 500g dex, 250g malt and 250g corn syrup, although someone may correct me!

Maybe low OG if you put in 1.25kg in the spreadsheet for the pilsner kit - its 1.7kg kit i think!

Good luck!. Its a nice beer. Mine tastes nothing like LCPA though!

 

[rehab]

LME is unhopped extract in Liquid malt form (as opposed to dry malt) where as the Kit you buy has been hopped to flavour to suit a certain stye.
LME is used in partial extract brewing in conjunction with grains (usually steeping spec malts from what I have read) and then boiling hops to be able to personalize your own tastes on the "kit"

I am just learning the way of the Partial Extract at the moment just for fun really. I did a few kits early on and skipped Partial brewing to go straight to AG but I didn't have a proper heating source. I have just purchased a Blichmann Burner but it isn't due for a few weeks yet so in the mean time instead of going back to kits I am trying out the "in between step".

 

[rehab]

Thanks and when you progress to BIAB there is a spreadsheet for that also.

cheers

Ian

Hi Ian you have done wonders for my brewing by creating the KtE Spreadsheet so many thanks for that. Could you tell me where can I see this BIAB spreadsheet?

 

[Stevedkiwi]

Hi Ian.



love your sheet it seems to do everything I want.



2 questions though, one of which is a bit minor but kinda bugs me. and the other suggests I might have stuffed something in the download/saving of the file.


1. Since I am currently using Coopers kits including the enhancer no2 I find that your sheets calcs of FG are way different than I am actually recording. It seems this is because the 250 g of Maltodextrine in the BE2 is being taken into a/c as a sugar wth 100% fermentability. so if I were to just put 1 lb (450g) of maltodextrine into a 1 gal (3.8 L) brew I get OG of 1.042 and a FG of 1.000. I think this should be 1.042 OG and say 1.040 FG as Mdextrine is only approx 3% fermentable.

I have looked at other software which either returns the same result as you do. i.e treating it as a sugar at 100% or treats it as a misc with no effect on OG or FG. So either everyone is wrong or just I am wrong. (Me thinks the latter, but this does remind me of my old RSM when I turned right instead of left and ended up staring at a whole regiment, "Stay where you are Davies .... You might be right and they might be wrong !!!!")


2. I tried the "make recipe" button which adds a line to the brews tab but the formulas seem to be mising so that I dont get an ABV etc even though I have entered a measured OG / FG



please dont think I am being critical, this is a great spreadsheet and I would just like your comments re point 1 and perhaps the fix to point 2.



thanks again for your great work



Steve D

 

[ianh]

Hi Ian you have done wonders for my brewing by creating the KtE Spreadsheet so many thanks for that. Could you tell me where can I see this BIAB spreadsheet?

Link post 30 has the most recent version.

cheers

Ian

 

[ianh]

1. Since I am currently using Coopers kits including the enhancer no2 I find that your sheets calcs of FG are way different than I am actually recording. It seems this is because the 250 g of Maltodextrine in the BE2 is being taken into a/c as a sugar wth 100% fermentability. so if I were to just put 1 lb (450g) of maltodextrine into a 1 gal (3.8 L) brew I get OG of 1.042 and a FG of 1.000. I think this should be 1.042 OG and say 1.040 FG as Mdextrine is only approx 3% fermentable.

I have looked at other software which either returns the same result as you do. i.e treating it as a sugar at 100% or treats it as a misc with no effect on OG or FG. So either everyone is wrong or just I am wrong. (Me thinks the latter, but this does remind me of my old RSM when I turned right instead of left and ended up staring at a whole regiment, "Stay where you are Davies .... You might be right and they might be wrong !!!!")


2. I tried the "make recipe" button which adds a line to the brews tab but the formulas seem to be mising so that I dont get an ABV etc even though I have entered a measured OG / FG

Hi Steve

Point 1 I assume you are using the latest version 4 as in the first post of the thread. The spreadsheet assumes that you will have something other than 100% maltodextrin in the brew. If you add the maltodextrin and then add say 0.1kg DME then you should get the FG you expected (uses 3% fermentability for maltodextrin).

Point 2 There should be a formula like =IF(AND(ISNUMBER(O2),ISNUMBER(P2)),((O2-P2)/7.45)+0.4,"") in cell Q2 (assuming you are using a hydrometer) of the BREWS worksheet. You may need to copy this formula down to any rows which don't have an %Alc.

cheers

Ian

 

[Stevedkiwi]

Thanks Ian,

1. Yes its the latest v.40 and yep i see that it calcs FG correctly when I add other ingrediaents (I knew there would be something I missed)


2. Found the formulas but they were on line 100 of the Brews tab.


3. One last question (I hope). What do the column headings F and R (In the brew tab) refer to ?


Cheers


Steve D

 

[ianh]

3. One last question (I hope). What do the column headings F and R (In the brew tab) refer to ?


Cheers


Steve D

Column F is the amount of Dried Malt extract used - cells C14+C15 on MAIN worksheet.

Column R is the Attenuation of the brew, the amount of sugars the yeast has converted to alcohol based on the OG and FG.

cheers

Ian