So in ZN, ZB,ZF I am having trouble entering in the values in Excel (which way to format the cells)
I think I see what you are saying... so in the ZN contract it would be in 64ths because you can trade in half point increments.
Do I use the Dollarfr function? To convert the prices I am taking from my charts
This bit... (INT(J2)*64+(J2-INT(J2))/0.005) ... turns the price in cell J2 into "number of ticks" price. Hence since minimum price increment is 1/64 then a price of 131 becomes 131*64 = 8384. It follows that 131,005 becomes 8385.
So @xplorer's formula becomes
=ROUND(IF(E2="Long",J2price - G2price, G2price-J2price, 0)
ie if Long then J2-G2 and if short G2-J2 but the prices are in ticks.
The following 2 users say Thank You to SMCJB for this post:
Since the price is quoted in 64ths and not decimals, we first need to know how many 64ths we have and then convert that to decimals. We find out how many 64ths we have by first dividing by 0.005 and then dividing by 64 to convert to decimals.
This post has been selected as an answer to the original posters question
I don't mean to intrude (again) in this thread because it seems like you may have found an answer to your question already, however since you posted this last question on my birthday I thought I'd take another stab at a possible solution for your spreadsheet.
As I mentioned in my previous post in this thread, Excel does have built in "Functions" to handle the conversion you are trying to work through here from Decimals to Fractions and from Fractions to Decimals.
These functions can simplify the statements you have to put into your spreadsheet which in turn can reduce the chance for errors in your manual inputs or due to rounding.
[So assuming you are using Excel]
If you want to convert a number that is stated as a Fraction [using your ZN example in 64th's] of 131'120 to a Decimal
The statement you would put into Excel would be: =DOLLARDE(131.120,64) resulting in 131.187500 in Decimal format
If you want to convert a number that is stated as a Decimal [using the same ZN example] of 131.187500 to a Fraction
The statement you would put into Excel would be: =DOLLARFR(131.187500,64) resulting in 131.120 in 64'ths
If you want to do the same calculations for the ZB you would simply substitute the ,64 in the above formulas with ,32
If you want to work on a spreadsheet for the ZF you would use ,128
It should go without saying but I'll say it anyway, the number I used in the above examples 131.120 could simply be replaced by a Cell Reference that uses the numbers you already have in your spreadsheet and then calculates your result.
I have attached a small Excel file with examples of the two Functions described here converting Decimal Format numbers to Fractions and vice versa.
I hope it helps
The following 4 users say Thank You to TopGunNote for this post: