Spreadsheet Advice for ZN pricing - Bonds and Interest Rates Trading | futures io social trading
futures io futures trading


Spreadsheet Advice for ZN pricing
Updated: Views / Replies:3,670 / 53
Created: by Hinneni Attachments:13

Welcome to futures io.

Welcome, Guest!

This forum was established to help traders (especially futures traders) by openly sharing indicators, strategies, methods, trading journals and discussing the psychology of trading.

We are fundamentally different than most other trading forums:
  • We work extremely hard to keep things positive on our forums.
  • We do not tolerate rude behavior, trolling, or vendor advertising in posts.
  • We firmly believe in openness and encourage sharing. The holy grail is within you, it is not something tangible you can download.
  • We expect our members to participate and become a part of the community. Help yourself by helping others.


You'll need to register in order to view the content of the threads and start contributing to our community. It's free and simple, and we will never resell your private information.

-- Big Mike

Reply
 13  
 
Thread Tools Search this Thread
 

Spreadsheet Advice for ZN pricing

  #31 (permalink)
Elite Member
san diego, CA/usa
 
Futures Experience: Intermediate
Platform: NinjaTrader
Favorite Futures: 6E
 
Posts: 22 since Oct 2014
Thanks: 9 given, 6 received


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

Reply With Quote
 
  #32 (permalink)
Market Wizard
Houston TX
 
Futures Experience: Advanced
Platform: XTrader
Broker/Data: Advantage Futures
Favorite Futures: Energy
 
Posts: 1,862 since Dec 2013
Thanks: 1,585 given, 2,804 received
Forum Reputation: Legendary


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
The following 3 users say Thank You to SMCJB for this post:
 
  #33 (permalink)
Elite Member
san diego, CA/usa
 
Futures Experience: Intermediate
Platform: NinjaTrader
Favorite Futures: 6E
 
Posts: 22 since Oct 2014
Thanks: 9 given, 6 received



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


???

Reply With Quote
 
  #34 (permalink)
Elite Member
san diego, CA/usa
 
Futures Experience: Intermediate
Platform: NinjaTrader
Favorite Futures: 6E
 
Posts: 22 since Oct 2014
Thanks: 9 given, 6 received

I think I am getting it

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!!!

Reply With Quote
The following user says Thank You to Hinneni for this post:
 
  #35 (permalink)
Market Wizard
Houston TX
 
Futures Experience: Advanced
Platform: XTrader
Broker/Data: Advantage Futures
Favorite Futures: Energy
 
Posts: 1,862 since Dec 2013
Thanks: 1,585 given, 2,804 received
Forum Reputation: Legendary


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
The following 2 users say Thank You to SMCJB for this post:
 
  #36 (permalink)
Market Wizard
London UK
 
Futures Experience: Beginner
Platform: CQG
Favorite Futures: Futures
 
xplorer's Avatar
 
Posts: 2,426 since Sep 2015
Thanks: 4,897 given, 3,446 received
Forum Reputation: Legendary


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
The following user says Thank You to xplorer for this post:
 
  #37 (permalink)
Market Wizard
Houston TX
 
Futures Experience: Advanced
Platform: XTrader
Broker/Data: Advantage Futures
Favorite Futures: Energy
 
Posts: 1,862 since Dec 2013
Thanks: 1,585 given, 2,804 received
Forum Reputation: Legendary


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
The following user says Thank You to SMCJB for this post:
 
  #38 (permalink)
Elite Member
san diego, CA/usa
 
Futures Experience: Intermediate
Platform: NinjaTrader
Favorite Futures: 6E
 
Posts: 22 since Oct 2014
Thanks: 9 given, 6 received

Not to beat a dead horse but...

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

Reply With Quote
 
  #39 (permalink)
Market Wizard
Houston TX
 
Futures Experience: Advanced
Platform: XTrader
Broker/Data: Advantage Futures
Favorite Futures: Energy
 
Posts: 1,862 since Dec 2013
Thanks: 1,585 given, 2,804 received
Forum Reputation: Legendary


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
The following 3 users say Thank You to SMCJB for this post:
 
  #40 (permalink)
Elite Member
Penetanguishene, Ontario, Canada
 
Futures Experience: None
Platform: platform
Favorite Futures: futures
 
Posts: 241 since Jun 2011
Thanks: 304 given, 665 received

Not to beat a dead horse but.....



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

Attached Files
Register to download File Type: xlsx Decimal to Fraction Function for ZN Contracts.xlsx (138.4 KB, 13 views)
Reply With Quote
The following 5 users say Thank You to TopGunNote for this post:

Reply



futures io > > > > Spreadsheet Advice for ZN pricing

Thread Tools Search this Thread
Search this Thread:

Advanced Search



Upcoming Webinars and Events (4:30PM ET unless noted)

FIO Journal Challenge featuring NinjaTrader ($2,000+ of prizes)

May
 

Portfolio Diversification w/Brendon Delate @ Diversify Portfolio

Elite only

An overview of volumetric analytical tools w/Sergey Sokolov @ PTMC

Elite only

John Hoagland (TBA)

Elite only

FuturesTrader71 Extended Ask Me Anything (AMA)

Elite only

Pete @ Jigsaw Trading (TBA)

Elite only

John @ No BS Day Trading (TBA)

Elite only

An Afternoon with FIO member Softsoap (being rescheduled)

Elite only
     

Similar Threads
Thread Thread Starter Forum Replies Last Post
bond pricing, beginner question fishtoeat Bonds and Interest Rates Trading 23 November 21st, 2015 04:38 PM
Good Advice on Bad Advice? afoxperson The Elite Circle 5 February 18th, 2015 10:36 PM
Daily volume on ES and ZN Identical but no Movement on ZN ? lexxmd Bonds and Interest Rates Trading 6 May 11th, 2014 11:54 PM
Can someone help with new TT pricing? Makololo Brokers and Data Feeds 0 October 12th, 2013 12:14 PM
Newbie Q: on Grain Pricing JVentura Commodities Futures Trading 4 January 19th, 2011 04:39 PM


All times are GMT -4. The time now is 08:56 AM.

Copyright © 2017 by futures io, s.a., Av Ricardo J. Alfaro, Century Tower, Panama, +507 833-9432, info@futures.io
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.
no new posts
Page generated 2017-05-25 in 0.15 seconds with 40 queries on phoenix via your IP 54.162.19.123