"freeze" an imported realtime updated cell value

[Guest]

Hi,
how can I "freeze" a given value of a cell which is updated on a realtime
basis, e.g., stock options prices imported from a sort of Bloomberg data
diffusion service? I have a worksheet that evaluates options strategies for
10 options series calculating implied volatility on a realtime basis. What I
need is, once the trader decides to enter a strategy, beginning prices must
be secured (values must be "frozen", de-activating the underlying updating
formulas), but all the same these very formulas should be restored once the
strategy is closed and options positions are zeroed, which happens maybe in
the very same day, or next week. I have 20 of such cells spread throughout
the workbook with 10 worksheets in Excel 2007. Any thoughts?
Thanks very much for any help.

 

[Bernie Deitrick]

Dan,

For simplicity and maintainability, move all your data links into one table, on a sheet named
"DataLinks".

Let's say that you have the links starting in cell A2, down to A21. In B2, use the formula

=A2

and copy that down to B21. Now you will have a 'doubled' table of data.

Here's the key point - Link all of your cells needing the values from the data links to the cells in
column B.

When you want to freeze the values, run this macro:

Sub Freeze()
Worksheets("DataLinks").Range("B2:B21").Value = _
Worksheets("DataLinks").Range("B2:B21").Value
End Sub

When you want to unfreeze the links, run this macro:

Sub UnFreeze()
Worksheets("DataLinks").Range("B2:B21").FormulaR1C1= _
"=RC[-1]"
End Sub

The links in column A are unaffected by these manipulations.

Assign the macros to buttons, and perhaps write the state to another cell, along the lines of:

Worksheets("Other Sheet").Range("StatusCell").Value = "Values FROZEN"
or
Worksheets("Other Sheet").Range("StatusCell").Value = "Values Active"


HTH,
Bernie
MS Excel MVP

 

[Guest]

Hi Bernie,
thanks a lot for your help. I put them in my workbook and tested (sort of).
It's really a very smart tip and think they'll do the job. I can't give you a
definite feedback though. Today is Independence Day and stock exchange is on
holiday. Get back to you Tuesday. Anyways, thank you very much.
--
Dan GSB
Asset Management - Rio

Dan,

For simplicity and maintainability, move all your data links into one table, on a sheet named
"DataLinks".

Let's say that you have the links starting in cell A2, down to A21. In B2, use the formula

=A2

and copy that down to B21. Now you will have a 'doubled' table of data.

Here's the key point - Link all of your cells needing the values from the data links to the cells in
column B.

When you want to freeze the values, run this macro:

Sub Freeze()
Worksheets("DataLinks").Range("B2:B21").Value = _
Worksheets("DataLinks").Range("B2:B21").Value
End Sub

When you want to unfreeze the links, run this macro:

Sub UnFreeze()
Worksheets("DataLinks").Range("B2:B21").FormulaR1C1= _
"=RC[-1]"
End Sub

The links in column A are unaffected by these manipulations.

Assign the macros to buttons, and perhaps write the state to another cell, along the lines of:

Worksheets("Other Sheet").Range("StatusCell").Value = "Values FROZEN"
or
Worksheets("Other Sheet").Range("StatusCell").Value = "Values Active"


HTH,
Bernie
MS Excel MVP

 

[Guest]

Hi Bernie,
sorry for being greedy but I forgot something that would be o great help.
Can these ranges A2:A21 and B2:B21 be dynamic. Point is that for one exercize
date, e.g. Oct, I may have options with strike prices 52, 54, 56 and 58, but
for Nov options series strike prices may increase or decrease, say, 56, 58,
60, 62, 64 and 66. Thanks again.
--
Dan GSB
Asset Management - Rio

Dan,

For simplicity and maintainability, move all your data links into one table, on a sheet named
"DataLinks".

Let's say that you have the links starting in cell A2, down to A21. In B2, use the formula

=A2

and copy that down to B21. Now you will have a 'doubled' table of data.

Here's the key point - Link all of your cells needing the values from the data links to the cells in
column B.

When you want to freeze the values, run this macro:

Sub Freeze()
Worksheets("DataLinks").Range("B2:B21").Value = _
Worksheets("DataLinks").Range("B2:B21").Value
End Sub

When you want to unfreeze the links, run this macro:

Sub UnFreeze()
Worksheets("DataLinks").Range("B2:B21").FormulaR1C1= _
"=RC[-1]"
End Sub

The links in column A are unaffected by these manipulations.

Assign the macros to buttons, and perhaps write the state to another cell, along the lines of:

Worksheets("Other Sheet").Range("StatusCell").Value = "Values FROZEN"
or
Worksheets("Other Sheet").Range("StatusCell").Value = "Values Active"


HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Dan,

I'm not sure why you would want the _ranges_ to be dynamic - those ranges are just the links to the
data sources. Your strike prices should be entered into other cells, and your calcs can be
dependent on the entry values in those cells, and the number of entries can vary.... but designing
around that requires more info than what you have just given....

HTH,
Bernie
MS Excel MVP

Hi Bernie,
sorry for being greedy but I forgot something that would be o great help.
Can these ranges A2:A21 and B2:B21 be dynamic. Point is that for one exercize
date, e.g. Oct, I may have options with strike prices 52, 54, 56 and 58, but
for Nov options series strike prices may increase or decrease, say, 56, 58,
60, 62, 64 and 66. Thanks again.
--
Dan GSB
Asset Management - Rio

Dan,

For simplicity and maintainability, move all your data links into one table, on a sheet named
"DataLinks".

Let's say that you have the links starting in cell A2, down to A21. In B2, use the formula

=A2

and copy that down to B21. Now you will have a 'doubled' table of data.

Here's the key point - Link all of your cells needing the values from the data links to the cells
in
column B.

When you want to freeze the values, run this macro:

Sub Freeze()
Worksheets("DataLinks").Range("B2:B21").Value = _
Worksheets("DataLinks").Range("B2:B21").Value
End Sub

When you want to unfreeze the links, run this macro:

Sub UnFreeze()
Worksheets("DataLinks").Range("B2:B21").FormulaR1C1= _
"=RC[-1]"
End Sub

The links in column A are unaffected by these manipulations.

Assign the macros to buttons, and perhaps write the state to another cell, along the lines of:

Worksheets("Other Sheet").Range("StatusCell").Value = "Values FROZEN"
or
Worksheets("Other Sheet").Range("StatusCell").Value = "Values Active"


HTH,
Bernie
MS Excel MVP

Hi,
how can I "freeze" a given value of a cell which is updated on a realtime
basis, e.g., stock options prices imported from a sort of Bloomberg data
diffusion service? I have a worksheet that evaluates options strategies for
10 options series calculating implied volatility on a realtime basis. What I
need is, once the trader decides to enter a strategy, beginning prices must
be secured (values must be "frozen", de-activating the underlying updating
formulas), but all the same these very formulas should be restored once the
strategy is closed and options positions are zeroed, which happens maybe in
the very same day, or next week. I have 20 of such cells spread throughout
the workbook with 10 worksheets in Excel 2007. Any thoughts?
Thanks very much for any help.

 

[Guest]

Bernie,
gave a thought and I guess I'll stick to 10 different strike prices. I'm
pretty sure that treating those ranges as dynamic is no big deal. But this
would unfold some real complications since I have spreadsheets with a defined
number of columns comparing pairs of options: K46:K48, K48:K50, ... It would
imply also a dynamic treatment for these tables, and that's a major leap I am
not willing to try at this stage.
Thanx a lot!
--
Dan GSB
Asset Management - Rio

Dan,

I'm not sure why you would want the _ranges_ to be dynamic - those ranges are just the links to the
data sources. Your strike prices should be entered into other cells, and your calcs can be
dependent on the entry values in those cells, and the number of entries can vary.... but designing
around that requires more info than what you have just given....

HTH,
Bernie
MS Excel MVP

Hi Bernie,
sorry for being greedy but I forgot something that would be o great help.
Can these ranges A2:A21 and B2:B21 be dynamic. Point is that for one exercize
date, e.g. Oct, I may have options with strike prices 52, 54, 56 and 58, but
for Nov options series strike prices may increase or decrease, say, 56, 58,
60, 62, 64 and 66. Thanks again.
--
Dan GSB
Asset Management - Rio

Dan,

For simplicity and maintainability, move all your data links into one table, on a sheet named
"DataLinks".

Let's say that you have the links starting in cell A2, down to A21. In B2, use the formula

=A2

and copy that down to B21. Now you will have a 'doubled' table of data.

Here's the key point - Link all of your cells needing the values from the data links to the cells
in
column B.

When you want to freeze the values, run this macro:

Sub Freeze()
Worksheets("DataLinks").Range("B2:B21").Value = _
Worksheets("DataLinks").Range("B2:B21").Value
End Sub

When you want to unfreeze the links, run this macro:

Sub UnFreeze()
Worksheets("DataLinks").Range("B2:B21").FormulaR1C1= _
"=RC[-1]"
End Sub

The links in column A are unaffected by these manipulations.

Assign the macros to buttons, and perhaps write the state to another cell, along the lines of:

Worksheets("Other Sheet").Range("StatusCell").Value = "Values FROZEN"
or
Worksheets("Other Sheet").Range("StatusCell").Value = "Values Active"


HTH,
Bernie
MS Excel MVP


Hi,
how can I "freeze" a given value of a cell which is updated on a realtime
basis, e.g., stock options prices imported from a sort of Bloomberg data
diffusion service? I have a worksheet that evaluates options strategies for
10 options series calculating implied volatility on a realtime basis. What I
need is, once the trader decides to enter a strategy, beginning prices must
be secured (values must be "frozen", de-activating the underlying updating
formulas), but all the same these very formulas should be restored once the
strategy is closed and options positions are zeroed, which happens maybe in
the very same day, or next week. I have 20 of such cells spread throughout
the workbook with 10 worksheets in Excel 2007. Any thoughts?
Thanks very much for any help.

 

[Guest]

Bernie,

Dan,

For simplicity and maintainability, move all your data links into one table, on a sheet named
"DataLinks".

Let's say that you have the links starting in cell A2, down to A21. In B2, use the formula

=A2

and copy that down to B21. Now you will have a 'doubled' table of data.

Here's the key point - Link all of your cells needing the values from the data links to the cells in
column B.

When you want to freeze the values, run this macro:

Sub Freeze()
Worksheets("DataLinks").Range("B2:B21").Value = _
Worksheets("DataLinks").Range("B2:B21").Value
End Sub

When you want to unfreeze the links, run this macro:

Sub UnFreeze()
Worksheets("DataLinks").Range("B2:B21").FormulaR1C1= _
"=RC[-1]"
End Sub

The links in column A are unaffected by these manipulations.

......
the macros work as a charm. Thanx. That's exactly what I was needing.

Assign the macros to buttons, and perhaps write the state to another cell, along the lines of:

Worksheets("Other Sheet").Range("StatusCell").Value = "Values FROZEN"
or
Worksheets("Other Sheet").Range("StatusCell").Value = "Values Active"

......
but running them it's not user-friendly and that's because I think I'm
confused with these 2 above codes. Where do I put them? As a code in the
above macros? Macros are not a key strength of mine. I do get that what you
mean is having a button in the worksheet associated to an underlying macro,
and by clicking said button macro is activated. But how???

HTH,
Bernie
MS Excel MVP

....
I wrote you that for the time being I'm not having the inputs as dynamic
ranges. I decided there's always going to be 10 series of options in
DataLinks A2:A11. But I do want to have a routine so that the user at the
beginning of the exercize period types the options labels (e.g. PETRJ52,
PETRJ54,...) sequentially in this range, remaining there until new exercize
period starts. However, Input Box procedure allows me only having 1 input at
a time and the corresponding cell must be highlighted manually. Is there a
way to have dialog box inputting all 10 entries in a row?

Thank you very much for your time and attention. Most kind of you.