Spreadsheet Advice for ZN pricing

September 28th, 2016, 08:52 AM

TopGunNote

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.

September 28th, 2016, 10:07 AM

xplorer

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

Yeah me either. Live & Learn

September 28th, 2016, 10:11 AM

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.

September 28th, 2016, 10:20 AM

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.

September 28th, 2016, 10:29 AM

SMCJB

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.

October 1st, 2016, 03:56 PM

xplorer

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

October 2nd, 2016, 03:31 AM

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

October 2nd, 2016, 05:42 AM

Hinneni

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?

October 3rd, 2016, 04:18 PM

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

October 3rd, 2016, 05:15 PM

Hinneni

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:

I have an older Excel version but the advice above should work anyway
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
If you want something more automated you may need to look into VBA

Hope this helps.

Spreadsheet Advice for ZN pricing

Discussion in Treasury Notes and Bonds

Spreadsheet Advice for ZN pricing