Every homebrewer asked himself at least once how long the fermentation will take or when its over / finished / completed. Therefore, this article will show how to forecast (or predict) the exact time and day when your fermentation will be over. To do so, only simple and free tools are needed (e.g. Excel or Open Office). This is important so you can clearly communicate to others whether or not you have time for non-brewing activities.
Track your fermentation
Tracking the fermentation can be done using bricks.bierbot.com and an iSpindel or TILT electric hydrometer. After recording your specific gravity (and temperature) during fermentation for some days will give a plot like this:
Note the Dropdown at the top where you can select specific timeranges you’re interested in. In this case (since fermentation is bottom-fermenting) things are going slow. This is why we chose 14d.
Get the data
To download the data, select the menu (three horizontal dashes) at the top-right corner of your gravity plot. Then, select download CSV.
A sample file can be downloaded here:
Use our Excel file
If you want to fast-forward to your date and time prediction, you can also use our Excel file and just insert your values. There is a chance, that you still have to adjust some stuff, but maybe you can re-use at least some parts.
Preparing the data
You can also try to skip this section and prepare the data in excel only. Depending on your systems configuration, it might be necessary to tweak some things in the CSV, e.g. to replace one character in the whole file (e.g. a “,” by a “;” if you’re living in Germany). To do so, you can e.g. get Visual Studio Code, navigate to one line with a comma, hold Ctr+Alt+Arrow-Down and get a multi-cursor. Then, you can delete the comma and set a semicolon for all lines with two key strokes.
🆘 If you need help, please leave a comment below – we’re here to help! ⛑
You can download the prepared sample file here:
Visualizing the data in excel and predicting
Preparing the data in Excel
Now, open the CSV, e.g. in Excel or Open Office Calc. This is where things get blurry and it’s impossible for me to cover all eventualities since Excel and dates are behave and look different depending on where you are. No matter where you are, the goal is to get one column, that is properly formatted as a date and another that translates that date to a number. The latter is important for our predictions to work properly. Here are some steps that might serve as guideline:
- Create one “TS string” column, that contains the “date + time”, e.g. “01.01.2021 18:12” or “01/01/2021 6:12pm”.#
- If excel split the date and time into two columns upon opening, you can merge them together using
=CONCATENATE(TEXT(A2,"mm/dd/yyyy"),B2). - Select that column > “format” > “custom format” and select “dd.mm.yyyy hh:mm”.
- Create a “TS number” column, which has the formula =C2*1 (assuming “TS string” is column C) and format it as number.
Next step: Select “TS number” and “Gravity” columns and insert a scatter / dot plot. Dot plot is important, for the timestamp column to be recognized as number.
Inserting a scatter / dot plot to predict completion
Then, right-click the line in the line plot and select “Add trend line”. Select “polynomial” / degree 2 (resulted into the best fit for me) and select the number of periodes to predicted in advance. I’ve picked 5. Depending on how your fermentation is already running, your value might differ.
You should get something like this
The plot tells us two things: First, our final gravity is probably going to be around 4°P (1.0157OG), second the SG will reach it’s lowest point at approximately “44416.7”. Now what’s “44416.7”? This value is the seconds since the 01/01/1970 (also called “UNIX time”). Let’s convert this value into a more human readable version using the following Excel formula:
=DATE(1970,1,1)+44416.7Click on the cell, format it as custom date using one of the strings above (e.g. “dd.mm.yyyy hh:mm”). And there you go: Prediction in our case is the “10.08.2091 16:48”. Hopefully, this answers the qestion “when will the fermentation be completed?”. Have fun planning the rest of your week 😉