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


Spreadsheet Advice for ZN pricing
Started: by Hinneni Views / Replies:3,266 / 53
Last Reply: 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

  #41 (permalink)
Market Wizard
London UK
 
Futures Experience: Beginner
Platform: CQG
Favorite Futures: Futures
 
xplorer's Avatar
 
Posts: 2,261 since Sep 2015
Thanks: 4,477 given, 3,119 received
Forum Reputation: Legendary


TopGunNote View Post
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

Thanks TGN, I wasn't familiar with these 2 functions.

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


xplorer View Post
Thanks TGN, I wasn't familiar with these 2 functions.

Yeah me either. Live & Learn

Reply With Quote
The following user says Thank You to SMCJB for this post:
 
  #43 (permalink)
Market Wizard
London UK
 
Futures Experience: Beginner
Platform: CQG
Favorite Futures: Futures
 
xplorer's Avatar
 
Posts: 2,261 since Sep 2015
Thanks: 4,477 given, 3,119 received
Forum Reputation: Legendary


Live & learn indeed... there's also the User Defined functions that one can create in Excel, but to be honest I can't be bothered to use Visual Basic

Probably a purist point of view but I try and avoid macros or code when I can.

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

I use a lot of user defined functions, saves rewriting long functions and makes reading my own spreadsheets a lot easier.

One word of caution, VBA functions actually compute significantly slower than raw excel formula's. It's unlikely anybody is connecting these spreadsheets to a latency dependent trading application, but if you are you probably want to stay away from functions.

Reply With Quote
The following user says Thank You to SMCJB for this post:
 
  #45 (permalink)
Market Wizard
London UK
 
Futures Experience: Beginner
Platform: CQG
Favorite Futures: Futures
 
xplorer's Avatar
 
Posts: 2,261 since Sep 2015
Thanks: 4,477 given, 3,119 received
Forum Reputation: Legendary

Futures Edge on FIO

SMCJB View Post
I use a lot of user defined functions, saves rewriting long functions and makes reading my own spreadsheets a lot easier.

One word of caution, VBA functions actually compute significantly slower than raw excel formula's. It's unlikely anybody is connecting these spreadsheets to a latency dependent trading application, but if you are you probably want to stay away from functions.

I guessed as much. To do away with the complexity I tend to create extra columns, so instead of having one big formula I have a series of smaller formulae and then I hide the columns I don't need.

Makes it easier to troubleshoot as well.

Reply With Quote
The following user says Thank You to xplorer for this post:
 
  #46 (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

Much Thanks!!!


xplorer View Post
Thanks TGN, I wasn't familiar with these 2 functions.



Belated Happy Birthday

Kind of getting more used to using Excel

It is fun to theorize about market activity and crunch numbers


thanks again

Reply With Quote
The following user says Thank You to Hinneni for this post:
 
  #47 (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

NR4 in excel

How would I write a function where the cell containing the narrowest range of the last four days turns red or something else to signal it in another way?


Thanks in Advance

Reply With Quote
 
  #48 (permalink)
Market Wizard
London UK
 
Futures Experience: Beginner
Platform: CQG
Favorite Futures: Futures
 
xplorer's Avatar
 
Posts: 2,261 since Sep 2015
Thanks: 4,477 given, 3,119 received
Forum Reputation: Legendary


Hinneni View Post
How would I write a function where the cell containing the narrowest range of the last four days turns red or something else to signal it in another way?


Thanks in Advance

In general you can use Conditional Formatting in Excel to get cells to change appearance based on values or a formula.

Can you post a screenshot of the cells and headings you want to change, to better understand what you want to achieve?

Reply With Quote
The following user says Thank You to xplorer for this post:
 
  #49 (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

NR4

I want to be able to notice the narrowest range of the last four sessions and when it occurs for that cell to change in appearance

I attached a screen shot of a spreadsheet for ZF

Attached Thumbnails
Spreadsheet Advice for ZN pricing-excel.png  
Reply With Quote
 
  #50 (permalink)
Market Wizard
London UK
 
Futures Experience: Beginner
Platform: CQG
Favorite Futures: Futures
 
xplorer's Avatar
 
Posts: 2,261 since Sep 2015
Thanks: 4,477 given, 3,119 received
Forum Reputation: Legendary



Hinneni View Post
I want to be able to notice the narrowest range of the last four sessions and when it occurs for that cell to change in appearance

I attached a screen shot of a spreadsheet for ZF

The spreadsheed attached does not contain dates, so I am going to assume that the 'last four sessions' are the 4 top ones.

What you are looking for is the type of Conditional Formatting highlighted in this screenshot (Top/Bottom rules --> Bottom 10 Items...)

Please register on futures.io to view futures trading content such as post attachment(s), image(s), and screenshot(s).



Next, in the dialog box you change the Bottom 10 into Bottom 1 as follows

Please register on futures.io to view futures trading content such as post attachment(s), image(s), and screenshot(s).



By applying this to the top 4 cells in column BA (Range) you will have the narrowest range highlighted in the color of your choice.


Notes:
  1. I have an older Excel version but the advice above should work anyway
  2. with this method every time you add a row you will need to readjust the conditional formatting, otherwise it may either expand to count more than 4 rows or it may start shifting down which you don't want
  3. If you want something more automated you may need to look into VBA

Hope this helps.

Reply With Quote

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)
 

Spring Grains Outlook w/Sean Lusk @ Walsh Trading

Elite only

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

April

Process above all else w/Anthony Crudele @ Futures Radio Show

Elite only

Machine Learning - Quantitative Trading w/Martin Froehler @ Quantiacs

Elite only

Market Dynamics w/Peter Davies @ Jigsaw Trading

Elite only

Ask Me Anything w/Patrick Rooney @ Trading Technologies

Apr 18

Ask Me Anything w/FuturesTrader71

Apr 19

Machine Learning w/Kris Longmore

Elite only

Market Analysis w/Dave Forss

Apr 25

Introducing iSystems with Stage 5 Trading

Apr 27
     

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 06:20 PM.

no new posts
Page generated 2017-03-27 in 0.15 seconds with 40 queries on phoenix via your IP 54.166.24.181