So I am trying to calculate break even points for various stock option strategies. I'd like to quickly enter the current price and the price of various call or put options and find out what my gain, loss, profit, return on margins at various strike prices and specifically the break even points. The break even points can be computed with the solver, but there doesn't seem to be any easy way that I am aware of to copy and paste the solver over multiple cells.

Any ideas?

2/16/2009 11:52:35 AM

Quote :

"It's about time you add a new skill set to your arsenal. Excel wasn't cut out for that kind of work."

2/16/2009 12:03:44 PM

I too am also looking for some help on setting up some excel sheets to run model portfolios and client accounts.

If anyone is excel savvy and might want to make a bit of spending money at school send me a pm to see if we could work something out.


[Edited on February 16, 2009 at 12:25 PM. Reason : .]

2/16/2009 12:25:14 PM

edit > go to > special > select formulas, paste.

I'm not sure if this is what you mean, I do not use the solver function.

diclaimer: pretty sure that is not right. ALSO would like a copy of a working excel program like this for kicks and giggles.

[Edited on February 16, 2009 at 12:40 PM. Reason : ergf]

2/16/2009 12:37:13 PM

^That's not what he means. I've used it, but only to help someone with some BS homework. Remember you can use $ in front of cell locations to make them static in a formula, so that it doesn't automatically skip to the next one over.

2/16/2009 12:42:01 PM

Is there a way to have excel populat itself based on real time data from a web site to put in end of day stock prices, with out having to put it in on each sheet or model port?

[Edited on February 16, 2009 at 12:45 PM. Reason : .]

2/16/2009 12:44:56 PM

eh, that's pushing it.. there might be a way to get a macro to do it but I highly highly doubt it would be worth it

[Edited on February 16, 2009 at 12:47 PM. Reason : a]

2/16/2009 12:47:15 PM

to get info from a web to excel... I have never done this.
http://office.microsoft.com/en-us/excel/HA010450851033.aspx

just tried it, works great with my google portfolio.

[Edited on February 16, 2009 at 12:54 PM. Reason : erg]

2/16/2009 12:49:50 PM

what is it that the Solver tool is doing in particular that can't be done with a regular formula or macro?
Does it do an iteration step function to find the break even? Can you replicate this behavior with a custom VBA Function and use that function in the cells?

2/16/2009 12:55:51 PM

Quote :

"Does it do an iteration step function to find the break even?"

Basically.

I have a function in one Cell, call it K8. The value of J8 that will make the K8 formula = 0 is my breakeven point. You click the solver tool, give it K8 as the target cell, give it J8 as the cell you want to vary, then you can give it other constraints (in this case it will be the range between bought puts which are below the strike price and sold calls which are above the strike price). What you are left with is a bare value in J8 of the breakeven point. I click record macro and see what it is recorded in an attempt to pull this into a macro but the function calls, SolverOk, SolverAdd, SolverSolve, aren't linked properly. So when I try to re-run the recorded macro, I get Compile Error, function not defined. Which, I just figured out with some googling here:

http://peltiertech.com/Excel/SolverVBA.html

So, I think I may be able to set up a macro now that will do what I need it to do.

2/16/2009 1:27:30 PM

Ok, after running into some bugs with parameter passing I finally settled on something like this

Sub SolverLoop()
   
   currRow = 7
   While Cells(currRow, 1).Value > 0
      SolverReset
      SolverOk setcell:=Cells(currRow, 11), MaxMinval:=3, ValueOf:="0", ByChange:=Cells(currRow, 10)
      SolverAdd CellRef:=Cells(currRow, 10), Relation:=3, FormulaText:=Cells(currRow, 3)
      SolverAdd CellRef:=Cells(currRow, 10), Relation:=1, FormulaText:=Cells(currRow, 5)
      SolverSolve UserFinish:=True
      currRow = currRow + 1
   Wend
End Sub

2/16/2009 2:37:55 PM