Stock Solution Design Spreadsheet .. StockSol.xls

pat w

Member
Nov 4, 2009
462
0
16
Daphne, AL (east Mobile Bay)
I'm gearing up for auto dosing. Previously I dosed KNO3,KH2PO4, and CSM+B as per EI recomendations, dry, but now I need to make up stock solutions (2) to feed my doser. I tried using Wet's (very usefull), APC's fertilator, Chuck Glad's, and Nutricalc. They all helped but I still had to do some paper and pencil work.

Now I'm no wizard with Excel but I can generate a formula when I need to so I thought I'd try my hand at a spreadsheet that would allow me to design any stock solution I wanted with the information I could gleen from the ap's listed above specific to any sized tank; all inclusive; no more work required.

It wound up looking like this:
attachment.php


Any comments would be welcome.

Pat
 
Last edited by a moderator:

Biollante

Lifetime Charter Member
Lifetime Member
Jun 21, 2009
3,210
3
36
Surprise, AZ
By Golly I Think You Have Something Here!

Hi Pat,

Wow! :cool: Even I can work this newfangled gadget and have an idea of what it means. :gw

I will check as best I can that the calculations are correct. :rolleyes:

Biollante
 

Tug

Lifetime Charter Member
Lifetime Member
Jan 5, 2009
1,150
9
38
Washington, DC
I think you have a great amount of time on your hands. :p
Well done.

FYI, I hear Wet's very useful calculator is a prototype for one yet to come with even more bells and whistles. Until then, here is another calculator to add to your list, http://www.fishfriend.com/fertfriend.html
My apple doesn't do spreadsheets, sorry.

Brain Trust
 
Last edited by a moderator:

pat w

Member
Nov 4, 2009
462
0
16
Daphne, AL (east Mobile Bay)
Thanks Bio, I used the values I got from APC's Fertilator for the conversion constants and tried to back check a few with the others. Those guys did all the heavy lifting there, so hats off to them.

Tug, Not so much. I wrote it over a few days off and on. I hope Wet gets his update done. His stuff is a real help when you're trying to understand what's going on in the tank. BTW the SS works with Open Office which has been ported for the Mac OSX Here and best of all IT'S FREE!

I'm working on a makeup solution calculator that will allow you to top off your supply and accurately modify the concentrations at the same time, but it's a little trickier.

Stay tuned

Pat
 

pat w

Member
Nov 4, 2009
462
0
16
Daphne, AL (east Mobile Bay)
Web Version

Here's a web friendly version for those who don't have Excel. Just download it , unzip it, and run the .xml file in your browser. (IE only at this time).

Pat
 

Attachments

  • StockSolWeb..zip
    4.4 KB · Views: 296

Wet

Lifetime Members
Lifetime Member
Aug 25, 2006
395
0
16
USA
Good job, Pat. I like your idea of making a spreadsheet so you can make solutions with several elements. Only a suggestion: it took me a minute to understand what was going on in the Stock Constants worksheet. Instead of using ppm/g/Gal, aka mg/L/g/Gal, which is non-intuitive, I'd suggest keeping that worksheet as % in mass or solution, then factor in the L to gal value in some separate cell. This will make it easier to add nutrients in the future. Good job!
 

pat w

Member
Nov 4, 2009
462
0
16
Daphne, AL (east Mobile Bay)
In the first incartation the "Stock Constants" sheet was hidden. I basically took APC's fertilator; set the tank volume to 1 gal. and entered either 1 gram or 1 ml for the component quantity and used the ppm value(s) it provided. I'm sure the changes you suggest make perfect sense,to a chemist, but I'm no chemist, not even close. For me this gets past the CHEMSPEAK that your suggestion involves. But that's just for ME, for NOW. I know as I continue, I'll need to educate myself in the principles involved.

If you or some other qaualified indivdual would care to assist, I'd be more than happy to make any changes toward improvement. Sounds like a very valid point.

Pat
 

Wet

Lifetime Members
Lifetime Member
Aug 25, 2006
395
0
16
USA
pat w,

Wow. That you reverse engineered other calculators that way is much more impressive than knowing Chemistry. So let's try to ignore the CHEMSPEAK and try talking another way.

Let's take your CSM+B stock constant of 17.25 for Fe, for example. This isn't a number most of us would use, in part because deriving it (reverse engineering it) would always depend on a calculator already doing what we need. Instead, we'd start with the spec sheet for CSM+B, which says it is 6.53% Fe. So, where's the 17.25 number come from? The easy answer is you multiplied that 6.53% Fe by 3.785 Liters per Gallon and then got to 17.25 ppm/g/gal. So, what if you took that 3.785 constant, which you use everywhere anyway, and made it its own cell. You add that constant to your formulas. You convert the current ppm/g(ml)/gal columns to percent mass. Now if some new company releases Super Awesome Plantbrain Sauce, which happens to be 13% Fe, you could just add that number to your spreadsheet.

(What's actually happening is % is 1/100, ppm is 1/100000, and you're talking in grams while they're talking in 1/1000 of a gram, but combining these constants makes for a spreadsheet that's easy to emss with, as you've done nicely here :) )
 
Last edited by a moderator:

pat w

Member
Nov 4, 2009
462
0
16
Daphne, AL (east Mobile Bay)
When the component is something like CSM or some other mix, the percent of mass should be easy enough to work out. It's the raw compounds (KNO3, MgSO4, and such) which involve researching the periodic table; working in Moles and Gram Molecular Weights and the like, that I'm not quite ready for YET.

Pat
 

pat w

Member
Nov 4, 2009
462
0
16
Daphne, AL (east Mobile Bay)
MakeupSol almost ready

As promised I've designed the Makeup solution spreadsheet. With it you'll be able to take a solution of a known recipe and modify the concentrations or even add new components and use most if not all of the remainder of the original solution as a starting point. You generate a makeup solution to sort of top off the original bringing the final solution to precisely the concentrations you desire. Using it will take some instruction so I'll be writing, doing screen shots, and the like. Could take a little while so bare with me.

Stay tuned.

Pat

P.S. I've added Iron 13% EDTA to StockSol and will upload the zipped file soon. I want to add a little more Iron to my solution to try and get my Red Ludwigia actually somewhere close to red.
 

pat w

Member
Nov 4, 2009
462
0
16
Daphne, AL (east Mobile Bay)
I started auto dosing a couple of weeks ago which prompted the development of StockSol but now I want to modify the recipe of my stock solutions to include more iron. I’ve recently added some Red Ludwigia that is an absolutely beautiful shade of GREEN so an increase of iron is indicated. I could just toss the remainder of the solution I’m dosing now and start from scratch but what a waste. I needed a method to adapt the remainder of the existing solution while simultaneously topping of the volume in general. I designed another spreadsheet to accomplish the task and offer it here with instructions for your review.

Let’s start with the original KNO3 CSM+B solution I’m currently using. I started with 1000 ml distilled water and added to that 80g KNO3 and 30g CSM+B dosed at a daily rate of 24 ml which should yield the following daily dosing levels in my 80gal. water column.

NO3 – 3.89 ppm
K – 2.45 ppm
FE – 0.16 ppm
Mg – 0.03 ppm

I’m also auto dosing KH2PO4 in a separate solution (10g in 1000 ml water) with nothing else so modifying the dose there is a simple task of adjusting the feed rate of the dosing pump. For reference the daily dose levels are as follows.

K – 0.23 ppm for a total of 2.68 ppm
PO4 – 0.55 ppm

With my uncalibrated eyeball I estimate I have about ½ or a little less of the original volume of the KNO3 CSM+B solution. I want to bring the total daily dose of iron up to 0.50 ppm and leave the remainder of the concentrations alone. I’d also like to top off the bottle to about 1500 ml give or take. The new recipe starting with a 1000ml distilled water base is determined by using StockSol.

attachment.php


I've got some Iron 13% EDTA on the way from Aquarium Fertilizers. Looks like I need to add 33g of it to get the iron levels up to the 0.50 ppm that I'm shooting for. Now for what to add to that to modify the existing solution to achieve the same concentrations in the final solution I'll need MakeupSol.


More to come ... Stay tuned.

Pat
 
Last edited by a moderator:

pat w

Member
Nov 4, 2009
462
0
16
Daphne, AL (east Mobile Bay)
So now I have a new recipe. I'll need distilled water, KNO3, CSM+B and Iron 13% EDTA. I'll need a container to mix it all in and if I make up 1000ml I know I'll need 80g of KNO3 and 30g of the CSM+B. I know this because this recipe has the same concentration of those components as the original solution I'll be adding to.

What I don't know right now is precisely how much of the old solution I have left. I'll pour out the solution into a container with trusted graduations and find … let's say … between 350ml and 400ml. If that's the best I can do with what I have I'll have to start with 350ml and discard the rest. Still a waste, but nothing to cry about. Now to open Excel (oops I'm on my home PC I have to use Open Office Calc) and load up MakeupSol.xls. and enter in the info.

You'll need to download and extract MakupSol.xls from the zip View attachment 1820.

attachment.php


Four tables … Ok? What to enter and where?

First the main quantities:

attachment.php



1.In the cell labeled 'Recipe volume' enter the base volume used to create the recipe in StockSol.
2.In the cell labeled 'Volume remaining' enter the volume you have left or, in this case, you are going to keep.
3.In the cell labeled 'Makeup volume' enter the amount of makeup solution water you plan to use.

Next you need to define the recipe:

attachment.php


Here you have the first opportunity to enter the names of the components in the recipe. Not necessary but it could help to keep track of the process. These cells can be anything or left blank with no effect. In the 'Grams' column enter the values in grams for the recipe you created in StockSol.

Next you need to define the components of the recipe that are included in the remainder of the existing solution:

attachment.php


This is done by placing a 1 in the column labeled 'In sol. Now (1/0)'. Notice that a 0 is in the column for the Iron. There is no Iron yet and if you placed a 1 there, the calculation for the amount needed to correct the existing solution for the absence of that component would not be made.

That's it. You're done. Check out your makeup solution.

attachment.php


Just take the values from the darker blue column labeled 'Total grams in makeup sol' for each of the components, add them to the water measured out for the makeup solution. Mix thoroughly and then add the completed makeup solution to the remaining existing solution. Your new stock solution is ready to dose. You should add two or three drops of anti-fungal medication to the mix to insure it doesn't get fungal growth if your solution includes Iron.

Well, That's all … right? …. Nope, there's those zero's in the '% increase' column. Wouldn't it be nice to just alter the concentrations of an existing solution and know exactly what's in it when you were done? … sure it would.

Next time … Stay tuned.

Pat
 

Attachments

  • MakeupSol.zip
    6 KB · Views: 261
Last edited by a moderator:

Singtoh

Prolific Poster
Sep 12, 2009
88
0
6
Bangkok,Thailand
Hello Pat,

Nice one:cool:, this will be really handy and a bit more economical, thanks for the spreadsheet. Just one question though(hopefully not too stupid, I am far from being a math or chemistry guru):D I am using Fleet Enema in my mixes, how do I go about putting that in the MakeupSol spreadsheet?

Cheers,

Singtoh
 

pat w

Member
Nov 4, 2009
462
0
16
Daphne, AL (east Mobile Bay)
Glad you found it usefull and no, not a stupid question at all.

Just enter the milliliters of Fleet Enema in the Recipe section where you would normally add grams of a dry component. In the Makeup section the milliliters of Fleet Enema needed for the Makeup solution will similarly be listed in the same column as the grams of dry components. MakeupSol will use either grams or milliliters as it deals with ratios and isn't concerned with the chemical specifics. Actually if you make sure you keep track of things you could substitute about any unit of volumetric measurement. Just remember that units you use to define the recipe will be the units MakeupSol will deliver. To prove it to yourself just enter the component values in tsp.. You can find a Gram to tsp conversion app. at APC in the Fertilator section.

Pat

P.S. Working on the next tutorial will ge it up as soon as I can.
 

pat w

Member
Nov 4, 2009
462
0
16
Daphne, AL (east Mobile Bay)
StockSol has been upgraded to StockSolPro (still free) with a new interface and new features. I've been fortunate enough get assistance from Wet in the form of some of his data from Yet-Another-Nutrient-Calculator and revamped the component entry with pull down lists and spin buttons for quantities.

****USES EXCEL VBA SCRIPTS so MS Excel is required.*** I'm attempting an Open Office solution but the feature set may need to be reduced.


- The table consists of 7 parameters (NO3, PO4, K, Ca, Mg, Fe, and Mn) and an additional 7 which are user select-able with pull down lists of their own.
- The first 7 are graphed against four major dosing programs (PMDD, Walstad, PPS, and EI) similar to the one Wet has in his calculator.
- An attempt has been made to make the quantity spin button increments dynamic to improve the animation of the parameter marker in the graph.
- Specifically because I auto dose with a continuous drip 24/7, I've added the option to calculate the EI portion of the graph to reflect the intent to dose on a 3 day-a-week or a 7 day-a-week schedule. (This has shown some interesting comparisons to PPS target ranges which is a native 7 day-a-week program.)
- Where available solubility is calculated as a percentage of the room temperature limit. (when not available the field will hold "#######")


Click the link below to download (zipped .xls)
https://www.zumodrive.com/file/download/571482578?mtime=1319590641

Comments as always are welcome.
Pat
 

Florin Ilia

Lifetime Charter Member
Lifetime Member
Mar 22, 2011
420
0
16
Bucharest, Romania
I can't download it. That website won't let me create an account, even after I installed their software.

But if you email it to me (florin at ilia . name), I'll host it for you (free of course).