Advice on calculating coupon dates?

I am wondering if anyone has approached this problem before. I'm looking for
advice more than code, but either would be appreciated.
Bonds periodically pay you a "coupon". Typically they do so on anniverseries
of their first coupon date (which we store) or last coupon date (maturity),
some number of times a year (typically 2, 4 or 6 times). I would like to run
a query that shows every bond that is going to pay a coupon within a certain
time, say one week on either side of today, or even just "today" would be
useful.
It would seem that I could potentially calculate this with some clever
"slicing" of the dates. For instance, if the coupon pays every month I could
substitute the month of the test date (July for instance) into the first/last
coupon date and get something reasonable. However this would only work if the
coupon is _this_ month - perhaps the closest one is at the end of last month?
Nor do I see an easy way to handle the non-monthly (or yearly) cases.
Any advice?
Maury
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:C585A270-C0A5-452B-85AC-56A8EFB4A2DA@.microsoft.com...
>I am wondering if anyone has approached this problem before. I'm looking
>for
> advice more than code, but either would be appreciated.
> Bonds periodically pay you a "coupon". Typically they do so on
> anniverseries
> of their first coupon date (which we store) or last coupon date
> (maturity),
> some number of times a year (typically 2, 4 or 6 times). I would like to
> run
> a query that shows every bond that is going to pay a coupon within a
> certain
> time, say one week on either side of today, or even just "today" would be
> useful.
> It would seem that I could potentially calculate this with some clever
> "slicing" of the dates. For instance, if the coupon pays every month I
> could
> substitute the month of the test date (July for instance) into the
> first/last
> coupon date and get something reasonable. However this would only work if
> the
> coupon is _this_ month - perhaps the closest one is at the end of last
> month?
> Nor do I see an easy way to handle the non-monthly (or yearly) cases.
> Any advice?
Break the problem into two problems. For every bond, calculate and store
ahead of time its cupon payment schedule.
create table BondCuponPayment
(
BondID int not null,
CuponDate DateTime not null,
constraint pk_BondPayment primary key (BondID, CuponDate),
Amount decimal(13,2)
)
Then you can run simple queries to retrieve upcoming cupon payments.
David
|||"David Browne" wrote:

> Break the problem into two problems. For every bond, calculate and store
> ahead of time its cupon payment schedule.
I didn't want to do this because it could potentially be an unlimited number
of bonds. So then I "got smart" and did another query on all the things that
we recently had a trade in. That was only 35, which seemed to be OK, it took
aboput 4 seconds.
But then on a lark I just did it for everything in the database. It took 0
seconds.
Sigh. Outsmarted myself again...
Maury

Advice on calculating coupon dates?

I am wondering if anyone has approached this problem before. I'm looking for
advice more than code, but either would be appreciated.
Bonds periodically pay you a "coupon". Typically they do so on anniverseries
of their first coupon date (which we store) or last coupon date (maturity),
some number of times a year (typically 2, 4 or 6 times). I would like to run
a query that shows every bond that is going to pay a coupon within a certain
time, say one week on either side of today, or even just "today" would be
useful.
It would seem that I could potentially calculate this with some clever
"slicing" of the dates. For instance, if the coupon pays every month I could
substitute the month of the test date (July for instance) into the first/last
coupon date and get something reasonable. However this would only work if the
coupon is _this_ month - perhaps the closest one is at the end of last month?
Nor do I see an easy way to handle the non-monthly (or yearly) cases.
Any advice?
Maury"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:C585A270-C0A5-452B-85AC-56A8EFB4A2DA@.microsoft.com...
>I am wondering if anyone has approached this problem before. I'm looking
>for
> advice more than code, but either would be appreciated.
> Bonds periodically pay you a "coupon". Typically they do so on
> anniverseries
> of their first coupon date (which we store) or last coupon date
> (maturity),
> some number of times a year (typically 2, 4 or 6 times). I would like to
> run
> a query that shows every bond that is going to pay a coupon within a
> certain
> time, say one week on either side of today, or even just "today" would be
> useful.
> It would seem that I could potentially calculate this with some clever
> "slicing" of the dates. For instance, if the coupon pays every month I
> could
> substitute the month of the test date (July for instance) into the
> first/last
> coupon date and get something reasonable. However this would only work if
> the
> coupon is _this_ month - perhaps the closest one is at the end of last
> month?
> Nor do I see an easy way to handle the non-monthly (or yearly) cases.
> Any advice?
Break the problem into two problems. For every bond, calculate and store
ahead of time its cupon payment schedule.
create table BondCuponPayment
(
BondID int not null,
CuponDate DateTime not null,
constraint pk_BondPayment primary key (BondID, CuponDate),
Amount decimal(13,2)
)
Then you can run simple queries to retrieve upcoming cupon payments.
David|||"David Browne" wrote:
> Break the problem into two problems. For every bond, calculate and store
> ahead of time its cupon payment schedule.
I didn't want to do this because it could potentially be an unlimited number
of bonds. So then I "got smart" and did another query on all the things that
we recently had a trade in. That was only 35, which seemed to be OK, it took
aboput 4 seconds.
But then on a lark I just did it for everything in the database. It took 0
seconds.
Sigh. Outsmarted myself again...
Maury

Advice on calculating coupon dates?

I am wondering if anyone has approached this problem before. I'm looking for
advice more than code, but either would be appreciated.
Bonds periodically pay you a "coupon". Typically they do so on anniverseries
of their first coupon date (which we store) or last coupon date (maturity),
some number of times a year (typically 2, 4 or 6 times). I would like to run
a query that shows every bond that is going to pay a coupon within a certain
time, say one week on either side of today, or even just "today" would be
useful.
It would seem that I could potentially calculate this with some clever
"slicing" of the dates. For instance, if the coupon pays every month I could
substitute the month of the test date (July for instance) into the first/las
t
coupon date and get something reasonable. However this would only work if th
e
coupon is _this_ month - perhaps the closest one is at the end of last month
?
Nor do I see an easy way to handle the non-monthly (or yearly) cases.
Any advice?
Maury"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:C585A270-C0A5-452B-85AC-56A8EFB4A2DA@.microsoft.com...
>I am wondering if anyone has approached this problem before. I'm looking
>for
> advice more than code, but either would be appreciated.
> Bonds periodically pay you a "coupon". Typically they do so on
> anniverseries
> of their first coupon date (which we store) or last coupon date
> (maturity),
> some number of times a year (typically 2, 4 or 6 times). I would like to
> run
> a query that shows every bond that is going to pay a coupon within a
> certain
> time, say one week on either side of today, or even just "today" would be
> useful.
> It would seem that I could potentially calculate this with some clever
> "slicing" of the dates. For instance, if the coupon pays every month I
> could
> substitute the month of the test date (July for instance) into the
> first/last
> coupon date and get something reasonable. However this would only work if
> the
> coupon is _this_ month - perhaps the closest one is at the end of last
> month?
> Nor do I see an easy way to handle the non-monthly (or yearly) cases.
> Any advice?
Break the problem into two problems. For every bond, calculate and store
ahead of time its cupon payment schedule.
create table BondCuponPayment
(
BondID int not null,
CuponDate DateTime not null,
constraint pk_BondPayment primary key (BondID, CuponDate),
Amount decimal(13,2)
)
Then you can run simple queries to retrieve upcoming cupon payments.
David|||"David Browne" wrote:

> Break the problem into two problems. For every bond, calculate and store
> ahead of time its cupon payment schedule.
I didn't want to do this because it could potentially be an unlimited number
of bonds. So then I "got smart" and did another query on all the things that
we recently had a trade in. That was only 35, which seemed to be OK, it took
aboput 4 seconds.
But then on a lark I just did it for everything in the database. It took 0
seconds.
Sigh. Outsmarted myself again...
Maurysql

advice for storing reservation/booking date ranges...

I am building a small app that will display availability data for properties. I have a calendar that displays the dates for a whole year in month rows with each days colour representing the availability status, i.e. booked, on hold etc.

My question is about how to store these dates in the db. At the moment I have the table below:

TABLE Availability
[PropertyID] [int] NOT NULL ,
[StatusID] [tinyint] NOT NULL ,
[StartDate] [smalldatetime] NOT NULL ,
[EndDate] [smalldatetime] NOT NULL

I was planning on having four status's for any given date, unknown, available, on hold or booked.

Displaying the dates has proved pretty simple but updating availability means I would need to query the db to see if any of the dates overlapped, I would then have to add the new date range/status as well as change any date ranges that overlapped, either in the sp or in the code and this is what made me wonder if there was a better way.

Does this sound a reasonable approach? Any advice or pointers would be greatly appreciated, This is the first time I have had to store date ranges and I want to make sure I am doing it right.

Instead of an Availability table, I would think you'd want a Booked table. Assume the property is available and create records in the Booked table when it's booked or held. Don't create records if you'd have an overlap by doing so. If no record for a time frame, then it's available.

It seems the way you're doing it, you would have to create a record for every date for every property. That makes it tough to be more granular if you need to go to the hour or minute level.

|||

Yes, unfortunately I have to cater for users who are not keeping the calendar uptodate hence using both the unknown and available status.

The default will be unknown, so anything not in the table will be that status and then it is up to users to use the calendar to update the properties availability, so there are three status's, available, on-hold and booked. These three will be in the table.

I think I will proceed as is and see how I go...thanks for the input.

Advanced lookup transformation

Hi,

Here is my problem for a lookup transformation:

I have an input flow with dates and fields like this :

ID BEGINNING_DATE ENDING_DATE

1 12/01/2006 12/16/2006

and a reference table like this:

ID PRICE BEGINNING_PRICE_DATE ENDING_PRICE_DATE

1 400 11/28/2006 12/03/2006

1 500 12/03/2006 12/06/2006

1 600 12/06/2006 12/09/2006

I have to get the intersection periods between the two tables joining on ID. I would like to have this result flow :

ID BEGINNING_DATE ENDING_DATE PRICE

1 12/01/2006 12/03/2006 400

1 12/03/2006 12/06/2006 500

1 12/06/2006 12/09/2006 600

I'm using a lookup transformation and modifying the SQL instruction in advanced tab like this:

select *
from
(select * from [dbo].[Price]) as refTable
where [refTable].[ID] = ?
and (? between [refTable].[BEGINNING_PRICE_DATE] and [refTable].[ENDING_PRICE_DATE]
or ? between [refTable].[BEGINNING_PRICE_DATE] and [refTable].[ENDING_PRICE_DATE]
or ([refTable].[BEGINNING_PRICE_DATE] between ? and ?
and [refTable].[ENDING_PRICE_DATE] between ? and ?))

In that case I have to define 7 parameters:

ID : parameter0

BEGINNING_DATE : parameter1, parameter3, parameter5

ENDING_DATE : parameter2, parameter4, parameter6

My problem is that the transformation looks for only one matching element and outputs 0 or 1 row per input row... In that case the 3 rows of my lookup table are matching with the row in my input table, but I have only the first one.

How could I do to resolve my problem?

Thanks... I hope it's clear...

Arnaud.

The lookup transformation can only output 1 row per 1 input row.

You would probably be best to load the data into a table and then join the 2 tables together.

You can simplyfy the join to

import.startDate < lookup.enddate

and import.endDate > lookup.startDate

This will match all records that overlap

|||

I think I have to keep my join because with your constraints I only have the price periods included in the imported periods and not those that overlap...

On the other hand I think I will create a temporary table for the input flow and join it to the lookup table as you recommend.

I wanted to avoid creating temporary tables but it seems to be the best solution here

Thanks a lot!

Arnaud.

|||

The conditions do allow for overlap.

In english they A and B overlap if A finshes after B starts and also A must start before B finishes. Thus the 2 criteria I posted.

|||

Yes Simon you're right!

My eyes were closed, sorry...

It simplyfies my sql request and all works fine, thanks a lot!

Arnaud.

|||No problem, Its a common scenario that many don't believe can be simplified to a single AND statement