NexusFi: Find Your Edge


Home Menu

 





Spreadsheet Advice for ZN pricing


Discussion in Treasury Notes and Bonds

Updated
      Top Posters
    1. looks_one Hinneni with 17 posts (6 thanks)
    2. looks_two Blash with 11 posts (13 thanks)
    3. looks_3 xplorer with 9 posts (6 thanks)
    4. looks_4 SMCJB with 8 posts (14 thanks)
      Best Posters
    1. looks_one Botts with 3.3 thanks per post
    2. looks_two SMCJB with 1.8 thanks per post
    3. looks_3 Blash with 1.2 thanks per post
    4. looks_4 Hinneni with 0.4 thanks per post
    1. trending_up 16,890 views
    2. thumb_up 54 thanks given
    3. group 6 followers
    1. forum 53 posts
    2. attach_file 13 attachments




 
Search this Thread

Spreadsheet Advice for ZN pricing

  #31 (permalink)
 Hinneni 
san diego, CA/usa
 
Experience: Intermediate
Platform: NinjaTrader
Trading: 6E
Posts: 22 since Oct 2014
Thanks Given: 9
Thanks Received: 6


SMCJB View Post
It's quoted in 32nds, but you can trade half points (which are then 64ths). So

Open 131,120 = 131 +12/32 [or 131.375 decimal]
High 132 = 132 [or 132 decimal]
Low 131,095 = 131 + 9.5/32 (same as 131 + 19/64) [or 131.297 decimal]
Close 131,315 = 131 +31.5/32 (same as 131 + 63/64) [or 131.984 decimal]

So High-Low = 132 - 131,095 = 132 - 131 9.5/32 = 22.5/32 = 22.5 ticks
[In Decimal 132 - 131.297 = 0.703 ~ 22.5/32]


Much thanks for your help!

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

Thanks again

Started this thread Reply With Quote

Can you help answer these questions
from other members on NexusFi?
Futures True Range Report
The Elite Circle
NT7 Indicator Script Troubleshooting - Camarilla Pivots
NinjaTrader
Deepmoney LLM
Elite Quantitative GenAI/LLM
The space time continuum and the dynamics of a financial …
Emini and Emicro Index
Ninja Mobile Trader VPS (ninjamobiletrader.com)
Trading Reviews and Vendors
 
Best Threads (Most Thanked)
in the last 7 days on NexusFi
Get funded firms 2023/2024 - Any recommendations or word …
59 thanks
Funded Trader platforms
36 thanks
NexusFi site changelog and issues/problem reporting
25 thanks
The Program
20 thanks
GFIs1 1 DAX trade per day journal
19 thanks
  #32 (permalink)
 
SMCJB's Avatar
 SMCJB 
Houston TX
Legendary Market Wizard
 
Experience: Advanced
Platform: TT and Stellar
Broker: Advantage Futures
Trading: Primarily Energy but also a little Equities, Fixed Income, Metals and Crypto.
Frequency: Many times daily
Duration: Never
Posts: 5,041 since Dec 2013
Thanks Given: 4,375
Thanks Received: 10,192


Hinneni View Post
Much thanks for your help!

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

Thanks again

You could just convert everything to a decimal by using something similar to what @xplorer's posted.

Specifically if your price is in Cell A1 then my decimal price would be INT(A1)+((A1-INT(A1))/0.005/64) or just INT(A1)+(A1-INT(A1))*3.125

Reply With Quote
Thanked by:
  #33 (permalink)
 Hinneni 
san diego, CA/usa
 
Experience: Intermediate
Platform: NinjaTrader
Trading: 6E
Posts: 22 since Oct 2014
Thanks Given: 9
Thanks Received: 6



SMCJB View Post
You could just convert everything to a decimal by using something similar to what @xplorer's posted.

Specifically if your price is in Cell A1 then my decimal price would be INT(A1)+((A1-INT(A1))/0.005/64) or just INT(A1)+(A1-INT(A1))*3.125


So 131,120 = 131 + 24/64 = 131.375 that would be the basic arithmetic...I thought there was some ecel function or something

High 132 - Low 131,095 (131 + 19/64) 131.296875 132 - 131.296875 = .703 then divide by 32

= .02196875


???

Started this thread Reply With Quote
  #34 (permalink)
 Hinneni 
san diego, CA/usa
 
Experience: Intermediate
Platform: NinjaTrader
Trading: 6E
Posts: 22 since Oct 2014
Thanks Given: 9
Thanks Received: 6

I plugged in Explorers function and it is working... I seem to be making things more difficult

I went over the thread closer and it helped it clear things up

I admit that I do not fully grasp the logic behind Explorers formula, though


Thanks everyone!!!

Started this thread Reply With Quote
Thanked by:
  #35 (permalink)
 
SMCJB's Avatar
 SMCJB 
Houston TX
Legendary Market Wizard
 
Experience: Advanced
Platform: TT and Stellar
Broker: Advantage Futures
Trading: Primarily Energy but also a little Equities, Fixed Income, Metals and Crypto.
Frequency: Many times daily
Duration: Never
Posts: 5,041 since Dec 2013
Thanks Given: 4,375
Thanks Received: 10,192


Hinneni View Post
I plugged in Explorers function and it is working... I seem to be making things more difficult

I went over the thread closer and it helped it clear things up

I admit that I do not fully grasp the logic behind Explorers formula, though


xplorer View Post
=ROUND(IF(E2="Long",(INT(J2)*64+(J2-INT(J2))/0.005)-(INT(G2)*64+(G2-INT(G2))/0.005),(INT(G2)*64+(G2-INT(G2))/0.005)-(INT(J2)*64+(J2-INT(J2))/0.005)),0)

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.

Reply With Quote
Thanked by:
  #36 (permalink)
 
xplorer's Avatar
 xplorer 
London UK
Site Moderator
 
Experience: Beginner
Platform: CQG
Broker: S5
Trading: Futures
Posts: 5,944 since Sep 2015
Thanks Given: 15,447
Thanks Received: 15,291


SMCJB View Post
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.

@Hinneni

Basically with this instrument to get from ticks to dollars you just multiply the tick value by 15.625

Reply With Quote
Thanked by:
  #37 (permalink)
 
SMCJB's Avatar
 SMCJB 
Houston TX
Legendary Market Wizard
 
Experience: Advanced
Platform: TT and Stellar
Broker: Advantage Futures
Trading: Primarily Energy but also a little Equities, Fixed Income, Metals and Crypto.
Frequency: Many times daily
Duration: Never
Posts: 5,041 since Dec 2013
Thanks Given: 4,375
Thanks Received: 10,192


xplorer View Post
@Hinneni

Basically with this instrument to get from ticks to dollars you just multiply the tick value by 15.625

Oh yeah.. that's kind of important! I never mentioned that!

Reply With Quote
Thanked by:
  #38 (permalink)
 Hinneni 
san diego, CA/usa
 
Experience: Intermediate
Platform: NinjaTrader
Trading: 6E
Posts: 22 since Oct 2014
Thanks Given: 9
Thanks Received: 6

INT(A1)+((A1-INT(A1))/0.005/64) or INT(A1)+(A1-INT(A1))*3.125

in the above formulas what is the reason for 0.005/64 or *3.125

I was breaking things down and am curious?




thanks

Started this thread Reply With Quote
  #39 (permalink)
 
SMCJB's Avatar
 SMCJB 
Houston TX
Legendary Market Wizard
 
Experience: Advanced
Platform: TT and Stellar
Broker: Advantage Futures
Trading: Primarily Energy but also a little Equities, Fixed Income, Metals and Crypto.
Frequency: Many times daily
Duration: Never
Posts: 5,041 since Dec 2013
Thanks Given: 4,375
Thanks Received: 10,192


Hinneni View Post
INT(A1)+((A1-INT(A1))/0.005/64) or INT(A1)+(A1-INT(A1))*3.125

in the above formulas what is the reason for 0.005/64 or *3.125

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.

Consider 131,005 which is 131 and 1/64.

INT(A1)+((A1-INT(A1))/0.005/64) becomes
INT(131,005) + ((131,005 - INT(131,005))/0.005/64) =
131 + ((131,005 - 131)/0.005/64) =
131 + (.005/0.005/64) =
131 + 1/64 = 131.015625

or

INT(A1)+(A1-INT(A1))*3.125
INT(131,005)+(131,005-INT(131,005))*3.125
131+(131,005-131)*3.125
131+0.005*3.125 = 131.015625

Reply With Quote
  #40 (permalink)
 
Botts's Avatar
 Botts 
Penetanguishene, Ontario, Canada
 
Experience: None
Platform: NinjaTrader-8
Broker: NinjaTrader Brokerage, Continuum
Trading: ZB, MES, NQ, YM
Posts: 924 since Jun 2011
Thanks Given: 4,019
Thanks Received: 3,605



Hinneni View Post
INT(A1)+((A1-INT(A1))/0.005/64) or INT(A1)+(A1-INT(A1))*3.125
in the above formulas what is the reason for 0.005/64 or *3.125
I was breaking things down and am curious?

thanks

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.
Attached Files
Elite Membership required to download: Decimal to Fraction Function for ZN Contracts.xlsx
Follow me on Twitter Visit my NexusFi Trade Journal Reply With Quote




Last Updated on October 4, 2016


© 2024 NexusFi™, s.a., All Rights Reserved.
Av Ricardo J. Alfaro, Century Tower, Panama City, Panama, Ph: +507 833-9432 (Panama and Intl), +1 888-312-3001 (USA and Canada)
All information is for educational use only and is not investment advice. There is a substantial risk of loss in trading commodity futures, stocks, options and foreign exchange products. Past performance is not indicative of future results.
About Us - Contact Us - Site Rules, Acceptable Use, and Terms and Conditions - Privacy Policy - Downloads - Top
no new posts