Static value function

[rstultz]

I'm creating an option trading workbook with approximately 70 option trading strategy worksheets. Each worksheet uses functions that dynamically pull live market data into a series cells. To evaluate profit or loss, opening and closing prices must be compared. I'm trying to find a way to save the opening value in a second cell for later reference. Tried using the =VALUE() function, but of course the value changes as the value changes in the referenced cell. Searched Help for static and freeze functions, but these do not exist for dynamically changing values. Any ideas on how to "freeze" the opening value other than having the worksheet users type the opening market value into a cell?
Thanks,
Russ

 

[FDibbins]

There are no formulas that will "save" a value to a cell, you would need VBA for this (not my strong side)

 

[Fazza]

hi, Russ

Maybe describe how you'd like it to happen - and that will help generate a solution that suits.

I imagine that VBA could be used to capture the opening value- and maybe store it in a defined name. BUT, I don't know what would trigger the VBA to run: or what conditions it would change the value. Hence my asking for you to describe how you'd like it to happen. Likely if you can describe it clearly/explicitly, someone can code it (or solve it somehow).

[My understanding isn't clear of the details, BTW. In my mind an opening value doesn't change all day, so there is something I don't understand. Maybe it is an opening value when the option is created, and with time (days/weeks/months) that opening value is what you're interested in, not a daily opening value.]

regards, Fazza

 

[Fazza]

PS. Maybe the opening value (as a fixed value) is available from the same place you pull the live data? So add a query that pulls it from the same place.

 

[J.Ty.]

If you use iterative calculations in Excel, you can record a value and keep it fixed as long as you need, without using VBA. Thread https://www.mrexcel.com/forum/excel-questions/816073-if-=-do-nothing.html contains several variants of such a solution.

J.Ty.

 

[rstultz]

Thanks for the suggestion, but since investors are often in trades for weeks, months, and even years, the opening prices also change daily.

 

[rstultz]

I'm using proprietary Excel functions that pull real-time market and option values from TD Ameritrade's thinkorswim trading platform. Values such as Last, IV%, Relative Strength Index, Bid, Ask, Mark, Delta, Theta, Open Interest, etc. are all pulled into cells of the worksheets, which all presently exist and work nicely. My goal is to minimize the amount of typing required by the option strategy worksheet users. So I began looking for a way to snag and store a few of the opening values for use in my current and final profit/loss formulas. But of course, since the values are constantly being updated by the market, I'm trying to find a way to find and store several opening values. I've tried a few IF statements by comparing the static date and time values to the dynamic NOW() date and time function values, but that was a bust. Tried comparing the dynamic value to zero with an IF statement, also doesn't work. Still "hacking away," but so far no success.

 

[btorrean]

Hey rstultz,

When is the workbook opened? At the beginning of the workday? Also, do you have it open all day? I'm trying to think of Excel opening or updating events that could trigger the data "pull".

Brian

Brian J. Torreano

 

[rstultz]

Thank you for your query,
The workbook can be opened anytime of the day. Of course the strategy worksheets are most instructive during market hours because users can actually watch the action of their simulated option trades in real time. Several responses have suggested IF statements, but since I'm pulling dynamically updated values from last price and option premium cells, they are constantly changing. Several respondents have suggested I need to use VBA. This will require me to brush up on my programming skills, which have been dormant for a few years. And, in addition to storing opening values into a few VBA number constants, I've got to figure out how to pull those values into the designated worksheet cells. Moreover, I'll have to buy a license to legally distribute VBA code to my audience rather than a "vanilla" workbook. Time permitting, I could do all of this. But it will delay the entire project while I "go to school."

 

[rstultz]

Are there any VBA code examples that can be used to store the opening value of a dynamically changing cell to a numerical constant and then poke that constant into a designated worksheet cell?
Thanks,
Russ