Excel calc problem

[grantx]

Assume I have =now() in A1. This gives me the current time (and date), eg 13:23:00 (the cell is formatted for time only).

Via VBA the sheet also downloads prices at regular intervals, eg 5 minutes; this time is recorded in column B,eg 13:20:00. From this I know the next price will downnload in 2 minutes.

I would like a cell, eg C1 to display the difference (countdown) between the current time and the next update (to the second). The obvious solution would be,

last time update+ 5 minutes - A1(current time).

However, despite rtfm, there doesn't seem to be a solution.

Any suggestions for cell or VBA solution greatly appreciated.

Grant.

 

[TWI]

There is always a solution with excel that is what makes it such a great tool.
How about using the timevalue(time_text) function

 

[Rhody Trader]

If the currenct time is in A1 and the last update is in B2, you can use the following formula for C1:

=TIME(HOUR(B2),MINUTE(B2)+5,0)-TIME(HOUR(A1),MINUTE(A1),SECOND(A1))

For it to really count down, though, your spreadsheet will need to refresh constantly.

 

[grantx]

TWI,

Thank you for the suggestion. This is the best I can do:

A1 = now() , eg 21:39:36
B1 = A1- last update (at 21:35:32) = 4:04, ie 4 min, 4 secs to next update.
This is an increasing function, but I need a decreasing function.

Using your suggestion, I can get

C1 = last update + timevalue(“0:05 AM”) = 25:39:32, ie the time of the next update.

All I need to do is subtract C1 from A1. But it isn’t allowed – all you get is ############

So what is so hard about = 4:04, 4 min, 4 secs to next update? Simple enough mental calculation but there are many more and of different durations – 10, 15, 30, 60 minutes.

The above was done prior to reading and trying Rhody’s suggestion but it’s included to illustrate a problem (and possibly my shortcomings).

Rhody,

That’s it. Thank you. I owe you, mate.

The refresh rate is currently five seconds. This is basically to test the viability of a real-time strategy so it isn’t an issue at the moment. It is simple to increase the rate if I need to. Thanks, once more.

Grant.

 

[swandro]

If you get ###### in a cell, all it means is that the column is not wide enough to display the value. If you widen the column you should see what it is trying to show you.

 

[grantx]

Swandro,

You are correct but this doesn't apply to my examples above. For whatever reason, and my Excel manual isn't clear on this, you cannot subtract a time value greater than =now() from =now(), eg =now() at 18:00:00 - 19:00:00 results in ########, not -01:00:00. Width adjust is irrelevant in this situation. Try it.

Grant.

 

[swandro]

Hi Grant

I guess I am missing the point. But I just tried what you suggested in your original message and I seemed to get the answer you wanted.

In A1 I have =NOW()
In B1 I have =NOW()-TIMEVALUE("00:01:00")
I have subtracted 1 minute on just for the sake of doing the test.

Then in C1 I have =B1+TIMEVALUE("00:05:00")-A1

When A1 showed 16/02/2007 00:12

B1 showed 16/02/2007 00:13

and C1 showed 00:04:00

The key is to make sure all the cells are formatted as Time. Then is should work.

Is this not what you are asking for?

swandro

 

[grantx]

Swandro,

I repeated your example and I think it duplicates one aspect of the problem, specifically the five-minute update interval (C1) is counting up:

A1 = 02:12:49, ie =now()
B1 = 02:07:49, =NOW()-TIMEVALUE("00:05:00")
C1 = 04:25:38, A1+TIMEVALUE("00:05:00")+C1

A few minutes later:

A1 = 02:32:10
B1 = 02:27:10
C1 = 05:04:20.

This is one of those really irritating little Excel problems which consumes an incommensurate amount of time and effort to solve (and I've been using Excel for sixteen years). We solved it, so forget it.

Here's another problem (I solved this one) I encountered this evening re accuracy of calculations. You may want to try it.

Minimum price for Bund futures is 0.01.

A1 = 114.92
B1 = 114.91
C1 = IF(A1-B1=0.01,"X",""), ie X or blank.

However, C1 = "", or blank, not X. Why is this? It seems that Excel treats 0.01as 0.0100000000000051. Therefore, 0.01 (C1) doesn't equal 0.0100000000000051.

Similarly, 0.02 actually equals 0.019999999999996.

I vaguely remember reading something re accuracy of Excel but thought it only relevant on a theoretical level.

I doubt many of us need to worry about accuracy when calculating profit or loss. If you are long or short 50,000,000 bunds and the bund changes 1000 points (10.00), the discrepany is Euro 0.0026 (if my maths is correct).

Grant.

 

[A Dashing Blade]

. . .
last time update+ 5 minutes - A1(current time).

Excel treats one day as 1.0 starting from 1 Jan 1900
So today (16 Feb 007) = 39129

Hours are treated as decimal of 1.0
ie 12 midday = 0.5

so, for 5 minute . . . 24 hours in a day, 60 minutes in a hour = 1/(24*60/5)

Wrt Now(), it's what it known as a "volatile" function, that is, it will recalculate itself whenever any other cell on the spreaddy recalculates. Thus, any cell that refers to the Now() cell will also recalc. This will impose serious overheads on large spreaddys.

I usually slap this bit of code in the Worksheet_Activate event (Assume there's a range "Today" on that sheet) . . .

Private Sub Worksheet_Activate()
if Me.Range("Today").Value <> Now then
Me.Range("Today").Value = Now
end if
End Sub

Wrt the rounding issues, use the Round() function

 

[rog1111]

...or the CEILING function

 

[new_trader]

Swandro,

I repeated your example and I think it duplicates one aspect of the problem, specifically the five-minute update interval (C1) is counting up:

A1 = 02:12:49, ie =now()
B1 = 02:07:49, =NOW()-TIMEVALUE("00:05:00")
C1 = 04:25:38, A1+TIMEVALUE("00:05:00")+C1

A few minutes later:

A1 = 02:32:10
B1 = 02:27:10
C1 = 05:04:20.

This is one of those really irritating little Excel problems which consumes an incommensurate amount of time and effort to solve (and I've been using Excel for sixteen years). We solved it, so forget it.

Here's another problem (I solved this one) I encountered this evening re accuracy of calculations. You may want to try it.

Minimum price for Bund futures is 0.01.

A1 = 114.92
B1 = 114.91
C1 = IF(A1-B1=0.01,"X",""), ie X or blank.

However, C1 = "", or blank, not X. Why is this? It seems that Excel treats 0.01as 0.0100000000000051. Therefore, 0.01 (C1) doesn't equal 0.0100000000000051.

Similarly, 0.02 actually equals 0.019999999999996.

I vaguely remember reading something re accuracy of Excel but thought it only relevant on a theoretical level.

I doubt many of us need to worry about accuracy when calculating profit or loss. If you are long or short 50,000,000 bunds and the bund changes 1000 points (10.00), the discrepany is Euro 0.0026 (if my maths is correct).

Grant.

Grant,

It has nothing to do with the accuracy of Excel because you will encounter the problem you have with any computer program. The reason you get this discrepancy is because computers work in Binary which is a base 2 system wheras humans work with base 10. Digits in successively lower, or less significant positions represent successively smaller powers of the Base numbering system.

eg/

10^0=1
10^1=10
10^2=100
10^3=1000

...etc

So 223 in decimal =

2 x 10^2=200 Plus
2 x 10^1=20 Plus
2 X 10^0=3


Binary works with Base 2 which yields

2^0=1
2^1=2
2^2=4
2^3=8
2^4=16
2^5=32
2^6=64
...etc



To obtain decimal values the inverse is taken of the above binary integers.

so

1/1=1
1/2=0.5
1/4=0.25
1/8=0.125
1/16=0.0625
1/32=0.03125
1/64=0.015625
1/128=0.0078125
1/512=0.001953125
1/1024=0.0009765625

0.01 is the inverse of 100 which is not a binary number. The computer calculates 0.01 by adding the inverse of other binary numbers, like 1/1024 + 1/512 + 1/128.

If you round your calculation to 0.01 increments you will find your formula works without a problem.

eg/

A1: =ROUND(114.92/0.01,0)*0.01
B1: =ROUND(114.91/0.01,0)*0.01
C1: =IF(ROUND((A1-B1)/0.01,0)*0.01=0.01,"X","")

 

[swandro]

New Trader

One of the reasons for data inaccuracy is that software uses floating point arithmetic. By definition, floating point arithmetic is inaccurate because you cannot accurately recreate the original values.

As you say, the correct process is always to round the answer to the required number of decimal places.

 

[grantx]

Db and Rog,

The =NOW() will only be referred in one cell (or four max), not in a range. Hence, no overhead problem.

Re functions. Aren’t integers more efficient than functions?

In my price difference example above (where there would be numerous cells), isn’t

=IF(A1-B1=0.0100000000000051,”X”,””)

better than your suggestion:

=IF(ROUND(A1-B1,2)=.01,”X”,””)


Grant.

 

[A Dashing Blade]

. . .

Re functions. Aren’t integers more efficient than functions?

In my price difference example above (where there would be numerous cells), isn’t

=IF(A1-B1=0.0100000000000051,”X”,””)

better than your suggestion:

=IF(ROUND(A1-B1,2)=.01,”X”,””)
. . . .

you can't guarantee that the value will be 0.0100000000000051

=IF(ABS(A1-B1)>whatever,”X”,””) would be a solution

but, tbh while yes, you're correct, I'd be gobsmacked if it made substantial difference which method you used.

 

[new_trader]

Db and Rog,

The =NOW() will only be referred in one cell (or four max), not in a range. Hence, no overhead problem.

Re functions. Aren’t integers more efficient than functions?

In my price difference example above (where there would be numerous cells), isn’t

=IF(A1-B1=0.0100000000000051,”X”,””)

better than your suggestion:

=IF(ROUND(A1-B1,2)=.01,”X”,””)


Grant.

I don't understand what you mean by integers are more efficient? If you divide 1 with 9 (Both are integers) you get 0.111111111....~(to infinity)

So you think it would be more efficient to fill all your cells with 0.111111....~ instead?

If whatever you trade moves in 0.01 increments then it's best to round in 0.01 increments, which isn't the same as rounding to 2 decimal places.

eg/ ROUND(114.92/0.25,0)*0.25

This does not round to 2 decimal places, it rounds in increments of the nearest multiple of 0.25

So if the answer was 1450.4178998 it would not round it to 1440.42 (2 decimal places) it would round it to 1450.50

Old Intel computers needed a seperate maths co-processor to speed up floating point calculations. Since the Pentium I think the co-processor was built in. You won't notice any difference in efficiency.

 

[new_trader]

you can't guarantee that the value will be 0.0100000000000051

=IF(ABS(A1-B1)>whatever,”X”,””) would be a solution

but, tbh while yes, you're correct, I'd be gobsmacked if it made substantial difference which method you used.

This is incorrect. ABS only treats negative and positive values as identicle.

 

[A Dashing Blade]

This is incorrect. ABS only treats negative and positive values as identicle.

er . . . yes, I know that and appreciate how a computer does it (ignores first/last bit of the byte whatever)

So use this rather than have to wrap the conditions up in an AND() function ie check for slightly over and under

(I'm assuming that the result could be 0.0000000001 over or under)

??

new_trader . . . I suspect grant meant that doing something in a cell was better than using excel functions in a cell. As a simple example, doing =2*2 is more efficiant than doing =2^2 as
use of the "^" operator resort to accessing a maths library.

 

[grantx]

New Trader,

Post #11. Impressive. I’ve printed it and will consider it more when I have the time.

A function is a pre-defined formula. If we have A1=10, B1=20, to sum these in C1 we can use =A1+B1 or =SUM(A1:B1). The first example adds the two figures, the second uses a formula to add the two figures, ie an extra layer, so to speak. That’s my thinking – if incorrect, please correct me – and why I questioned efficiency.

Re “infinity”. My examples were specific – the possibility of an infinite number won’t arise, only 0.01 increments.

Your =ROUND solution does work. However, since I’ve already corrected the discrepancy/anomaly, I’m not going to change it all again. But I have made a note of it.

Thank you.

Grant.

 

[grantx]

Db,

“you can't guarantee that the value will be 0.0100000000000051”. If the result should be 0.01 but so far produces 0.0100000000000051, are you saying the inaccuracy is not constant, even erratic? If code says, “Add this number to this number”, it can’t divide the numbers - it can only do as instructed. There may be an flaw in coding or the “Binary/base 2 system” above which produces erroneous results, but this is a different matter, isn't it?

Grant.