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

  #41 (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


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
Thanked by:

Can you help answer these questions
from other members on NexusFi?
Futures True Range Report
The Elite Circle
The space time continuum and the dynamics of a financial …
Emini and Emicro Index
Better Renko Gaps
The Elite Circle
Online prop firm The Funded Trader (TFT) going under?
Traders Hideout
My NT8 Volume Profile Split by Asian/Euro/Open
NinjaTrader
 
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
22 thanks
The Program
20 thanks
GFIs1 1 DAX trade per day journal
19 thanks
  #42 (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
Thanks TGN, I wasn't familiar with these 2 functions.

Yeah me either. Live & Learn

Reply With Quote
Thanked by:
  #43 (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


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

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
Thanked by:
  #45 (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
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
Thanked by:
  #46 (permalink)
 Hinneni 
san diego, CA/usa
 
Experience: Intermediate
Platform: NinjaTrader
Trading: 6E
Posts: 22 since Oct 2014
Thanks Given: 9
Thanks Received: 6


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

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

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

Started this thread Reply With Quote
  #48 (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


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
Thanked by:
  #49 (permalink)
 Hinneni 
san diego, CA/usa
 
Experience: Intermediate
Platform: NinjaTrader
Trading: 6E
Posts: 22 since Oct 2014
Thanks Given: 9
Thanks Received: 6

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
Click image for larger version

Name:	Excel.PNG
Views:	261
Size:	73.4 KB
ID:	218239  
Started this thread Reply With Quote
  #50 (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



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...)




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




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




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