Volume - absolute and relative

Please look at the figures below.

A)
Bid volume = 2000
Ask volume = 1000
bid/ask ratio = 2:1


B)
Bid volume = 4000
Ask volume = 2000
bid/ask ratio = 2:1

In relative terms both bid/ask ratios are equal but the volume for B) is double that in A). How would I distinguish the two in terms of a ratio?

Thank you.

Grant.

grantx said:

Please look at the figures below.

A)
Bid volume = 2000
Ask volume = 1000
bid/ask ratio = 2:1


B)
Bid volume = 4000
Ask volume = 2000
bid/ask ratio = 2:1

In relative terms both bid/ask ratios are equal but the volume for B) is double that in A). How would I distinguish the two in terms of a ratio?

Thank you.

Grant.

Click to expand...

ummm 4:2 should help you distinguish???

JK

JK,

Thank you for the reply.

Minor point – the ratio is expressed as a decimal, eg 2:1 = 2.0.

Repeating the above figures in Excel:

A1 = 2000
A2 = 1000
A3 = A1/A2 (=2.0)

B1 = 4000
B2 = 2000
B3 = B1/B2 (=2.0).

I was thinking we could reduce individual values by dividing by 1000, eg

4000/1000 = 4
2000/1000 = 2

and then possibly concatenate but I can’t figure a solution.

Obviously, I’m not seeing your solution. Perhaps you could elaborate.

Grant.

grantx said:

JK,

Thank you for the reply.

Minor point – the ratio is expressed as a decimal, eg 2:1 = 2.0.

Repeating the above figures in Excel:

A1 = 2000
A2 = 1000
A3 = A1/A2 (=2.0)

B1 = 4000
B2 = 2000
B3 = B1/B2 (=2.0).

I was thinking we could reduce individual values by dividing by 1000, eg

4000/1000 = 4
2000/1000 = 2

and then possibly concatenate but I can’t figure a solution.

Obviously, I’m not seeing your solution. Perhaps you could elaborate.

Grant.

Click to expand...

I know nothing about excell spreadsheets and it wasn't clear in your first post that is what you are referring too

You said:

2000/1000 is 2:1
4000/2000 is also 2:1

How do you tell the difference.. well the logical answer is 2:1 and 4:2 but that is stating the odvious and clearly not what your after

JK

Grant.

JK,

I think I've got it.

Originally:

A)
Bid volume = 2000
Ask volume = 1000
bid/ask ratio = 2:1

Solution:

Bid vol = 2000
log(2000) = 3.301
log(3.301) = 0.519

Ask vol = 1000
log(1000) = 3.000
log(3.000) = 0.477
0.519 + 0.477 = 0.996

B)
Bid vol = 4000
Ask vol = 2000
= 1.075

Extreme example:

Bid vol = 15,000
Ask vol = 1000
= 1.098


Grant.

grantx said:

JK,

I think I've got it.

Originally:

A)
Bid volume = 2000
Ask volume = 1000
bid/ask ratio = 2:1

Solution:

Bid vol = 2000
log(2000) = 3.301
log(3.301) = 0.519

Ask vol = 1000
log(1000) = 3.000
log(3.000) = 0.477
0.519 + 0.477 = 0.996

B)
Bid vol = 4000
Ask vol = 2000
= 1.075

Extreme example:

Bid vol = 15,000
Ask vol = 1000
= 1.098


Grant.

Click to expand...

Grant,

Did this provide what you wanted? I honestly can't understand why you used the LOG function. I can only surmise that I misunderstood your original question because I certainly don't understand the solution!

LOG (logarithm) of a number gives you the number which you must raise a base number (in this case base=10) to the power of to get that number. It has nothing to do with ratios LOG 1000 = 3 because 10^3 = 10 x 10 x10 = 1000.

What you wanted (I thought) was to distinguish between 4000/2000 & 2000/1000 because they both = 2, even though 4000 is double 2000.

Normally, in the absence of an elegant solution, an inelegant one will do. The way I would solve the problem would be to have variables, bid_old and bid_new (or something like that)

I would then do a simple calculation: bid_old/bid_new

in the above case, bid_old= 4000 and bid_new=2000

bid_old/bid_new = 2


Naturally, ignore this message if I am completely off the mark.

NT,

The log solution was a guess. The result works, ie bid 4000, ask 2000 is greater (relatively – or is it absolutely?) than bid 2000, ask 1000. That's what I was trying to differntiate.

I don’t understand the solution either (just don’t tell me it’s wrong).

Grant.

grantx said:

I was thinking we could reduce individual values by dividing by 1000, eg

4000/1000 = 4
2000/1000 = 2

and then possibly concatenate but I can’t figure a solution.

Grant.

Click to expand...

Well you CAN do what you suggested above - surely that is the solution? Firstly, divide by 1000, then round to a whole number (if you want to), then concatenate the results.

e.g. =CONCATENATE(ROUND(A1/1000,0),"/",ROUND(B1/1000,0)) where A1 and B1 contain the 2 numbers.

Swandro,

Thank you for the suggestion. I'll give it a go.

Grant.