Welcome to NexusFi: the best trading community on the planet, with over 150,000 members Sign Up Now for Free
Genuine reviews from real traders, not fake reviews from stealth vendors
Quality education from leading professional traders
We are a friendly, helpful, and positive community
We do not tolerate rude behavior, trolling, or vendors advertising in posts
We are here to help, just let us know what you need
You'll need to register in order to view the content of the threads and start contributing to our community. It's free for basic access, or support us by becoming an Elite Member -- see if you qualify for a discount below.
-- Big Mike, Site Administrator
(If you already have an account, login at the top of the page)
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
Trading: Primarily Energy but also a little Equities, Fixed Income, Metals and Crypto.
Frequency: Many times daily
Duration: Never
Posts: 5,057 since Dec 2013
Thanks Given: 4,399
Thanks Received: 10,225
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.
Trading: Primarily Energy but also a little Equities, Fixed Income, Metals and Crypto.
Frequency: Many times daily
Duration: Never
Posts: 5,057 since Dec 2013
Thanks Given: 4,399
Thanks Received: 10,225
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.
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
TGN
R.I.P. John Bottomley (Botts), 1956-2022.
Please visit this thread for more information.