Freezing the dynamic quote into a seperate cell

[Bucksy]

Good day,

This question relates to triggered trading with Excel. I thought the formular
=IF(D2=AA4;"F5";"")
does it but it doesn't. Hopefully some one can help.

I urgently need to freez the dynamic Odd1 at particular pre-set times during In-Play events into a seperate cell (i.e. in the AB colum) wheras the pre-set times for the "quote-shots" shall be manually defined in the AA colums.

The first odd1-mark shall be made and reported when the event turns from Not In Play into In Play. I know how to set the flag resulting a 1 into my flag cell for "switching on" the report period.
Since I want to report the odd1 quote every 10 minutes I need the static quotes freezed in a seperate cell to use the fixed quotes for further formulars to set the back/lay triggers.

In brief: The D2 times, when equal the pre-set capture times, shall freez the F5 value at the particular pre-set times in a seperate cell.

I very much appreciate your support.

Have a great day,
Frank

[milfor]

I don't think that it is possible to 'freeze' something with spreadsheet formulas because they are designed to update all the time. I would have a look at Visual Basic or ask in the developers forum.

[silver]

Bucksy

In your example above, if you wanted the cell which bears the formula to state the F5 value when D2=AA4 AND keep displaying that same value AFTER D2 ceases to equal AA4, you could do it like this:

(In this example the formula is in cell CA4)

=IF(D2=AA4,F5,CA4)

So, the factor you were missing in your experiment was that the formula must refer to the value of its own cell (CA4 in this example).

In this example, the cell contents should be overwritten each time D2=AA4. If you need the cell contents to 'reset' each time a new market loads (for example), you would need to add further criteria to your formula.

I use Excel 2007 and I am not sure that all earlier versions of Excel can accommodate formulas referring to the host cell in this way.

A developer could definitely provide you with an elegant and effective piece of code to do what you want, but if you want to keep experimenting with formulae, perhaps this will point you in the right direction.

[Ian]

That would be a circular reference in older versions of Excel.

[orange]

I could be wrong but I think the request is for a column that contains all the figures after a snap shot every 10 mins that can then be used to work out if the odds are shortening then do this or that or if they are going out then do this or that as opposed to just if that figure is more or less than x then do this or that.

All of which would need doing with code and not a formula if I am not mistaken.

Sorry if I am wrong, just trying to help.

[Bucksy]

Dear all;

Thank you very much for paying attention to my post and the energy you spent to give me a jump.

Milfor: You are pretty sure right but I still need to test and adjust and since I am not experienced with VBA I thought I find out if there is an option in Excel to adjust the screw to match the final system.

Silver: The formula given by you doesn't work out at all and Excel gives me a pop-up advising that there is a mistake in the formula. Did I miss anything or is there a little bug hiding in the formula you provided. I really appreciate your efforts.

Ian: I run Office 2007

Orange: You hit the nail right on its head.
I need a static Odd1 every ten minutes that remains in the new cell for further flags that trigger the bet at a particular time.
I need 5 records within the first 45 minutes of a game, and one more after 5 minutes of the second half of the game.
Actually, the 10 minutes record with decreasing odd1 quotes indicates the traditional down trend in a soccer game until one team scores. Any score will change the trend to go up.
Every odd1 quote increased will indicate at least one score. By having the code I am asking for I will have a wonderful livescore result right in the spreddsheet that I can combine with my automated triggers which provides me very valuable information to either pull the back/lay trigger or not.

I have an off-line spreadsheet prepared explaining the needs much better and in details. If someone likes to have a look at it just let me know.
I spent a lot of time reading the chart fingerprints of soccer games and I really don't play but know why I do need the static odd1 records.

Any subsequent help is very welcome.

Frank[/img]

[silver]

Bucksy

I think you need to enable Iterative Calculations in the Formulas area of the Preferences section.

Just in case you're not sure where they are...

Click the MS Office icon in the top left
Select 'Excel Options' at the bottom of the box that pops up
Select 'Formulas' on the left
Tick the box next to 'Enable iterative calculation'
Setting maximum iterations to 1 should be OK

Hopefully this will fix the problem.

[Bucksy]

Silver;

I did what you advised and adjusted the iterative calculation preferences
from max iterative number 1000 down to 1.
Max change remains 0,001

When leaving your formula as given
=IF(D2=AA4,F5,CA4)
I still get the same formula error message instructing me to go to the help section.

If I change your formula to
=IF(D2=AA4;F5;CA4) [changed the commas to semi-colons]
the cell shows #NAME?

It was just a try to replace the commas to semi-colons.

Sorry, problem not fixed.

[Captain Sensible]

I have a sheet that logs prices using vba at set times if that's any use

[Captain Sensible]

Got to log off so here's the code I used, not sure how it's work for minus times but shoulkdn't be too hard to overcome

Private Sub Worksheet_Change(ByVal Target As Range)
lastrow = Target.Rows.Count
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
If Cells(1, 26) > Cells(4, 38) Then
If Cells(1, 26) < Cells(3, 38) Then
For i = 5 To 35
Cells(i, 30) = Cells(i, 15)
Next i

End If
Application.EnableEvents = True

Else

Application.EnableEvents = True
End If


If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
If Cells(1, 26) > Cells(6, 38) Then
If Cells(1, 26) < Cells(5, 38) Then
For i = 5 To 35
Cells(i, 31) = Cells(i, 15)
Next i

End If
Application.EnableEvents = True

Else

Application.EnableEvents = True
End If


End Sub


Cells(1, 26) refers to Z1 where I had a copy of the time (+D2) just cos I'd hidden some columns so things were easier to see on my sheet

I'd entered the times I want to start and stop logging in column AL i,e Cells(4, 38) AL3 had 1st start time, Cells(3, 38) AL4 end logging time

The code would then just log Cells(i, 30) = Cells(i, 15) i'e put the last traded price( Cells(i, 15) ) into column AD ( Cells(i, 30) )

[silver]

Bucksy

I've prepared a very simple spreadsheet which shows the calculation structure in action (not a triggered betting sheet, just a simple example).

If you send me a private message through the forum system to tell me your email address, I will email it to you and perhaps we can identify what problems may remain in preferences etc.

[Bucksy]

Silver:

You are the best. Thank you very much.
My Excel is a German version so IF = WENN.
I changed this one AND the commas to semi-colons.
It seems to work perfectly now. Just had my first test run.

Man you made me very happy. Thank you so much.

[silver]

That's great, Bucksy

I hadn't considered that non-English versions might have different formula names or different conventions for separating terms.

Hopefully the formula will do what you need in its present form, but if you find it needs adjustment I am happy to help, if necessary.

Silver