Trade Turnover Formula 2

[reidtw]

Help!

From a transaction file I need to determine if stock has been sold within 2 months of being bought.

Here is some simple data: -

assetcode tradedate tradetype
ASSET01 20040130 BUY
ASSET02 20040130 BUY
ASSET01 20040215 SELL
ASSET03 20040130 BUY
ASSET04 20040130 BUY
ASSET03 20040515 SELL

I need to show the buy and sell details on the same line in the report.

Any assistance would be gratefully received.

Cheers
T

 

[synapsevampire]

Do you only want to show those that are within 2 months?

Try posting technical information, such as:

Crystal version
Database/connectivity used
Example data (you did this)
Expected output (you opted to describe it instead)

Try the following:

Group by the ASSETCODE

Now you can place the following formulas in the group header or footer:

minimum({table.Date},{table.assetcode})
maximum({table.Date},{table.assetcode})

To test for the age, use:

datediff("m",minimum({table.Date},{table.assetcode}),maximum({table.Date},{table.assetcode})) < 3

Or you can highlight them, or remove them from the report, or whatever.

Since you gave no indication of how you intend to display, rather stated that you need to determine, the above determines.

-k

 

[reidtw]

Hi,

Many thanks for the info, apologies for my vagueness....

Using CRv8
Connecting thru' ODBC SQL Server
and here is some data....

Fund FUND0001
Asset Code ASSET001
Asset Name COMPANY ORD SHS
Buy Trade Date 04/08/04
Buy Ref TRADE0023
Buy Nominal 147,005
Sell Trade Date 26/08/04
Sell Ref TRADE0154
Sell Nominal 75,004

Cheers
T

 

[reidtw]

Hi -k,

Further thoughts about data selection....

1. It is likely there will be multiple purchases and sales for the same asset, so selecting the min & max is not sufficient, I need to be able to select the min of the purchases and then the max of the sales, eliminating the other deals.

I've tried this in the group section suppression option, the group selector and in the suppression option of the max/min date formula I created using the following: -

maximum({TradeMarketSide.dtDeal},{TradeMarketSide.sBuySell} = "S") or
minimum({TradeMarketSide.dtDeal},{TradeMarketSide.sBuySell} = "B")

but I keep getting the message "The summary / running total field could not be created".

2. Although not essential, I would like the puchase and sale details to be on one line for each asset.

Cheers
T

 

[reidtw]

Hi synapsevampire,

Wondered if you would be able to pick up on this again?

Cheers

T

 

[lbass]

You should be grouping on {table.AssetCode}. Then create two formulas:

//{@Buy}:
if {table.tradetype}= "Buy" then {table.tradedate}

//{@Sell}:
if {table.tradetype}= "Sell" then {table.tradedate}

Then right click on each formula. Insert a minimum on {@Buy} and a maximum on {@Sell}. Then suppress the details section. To get the difference, use a formula in the group footer:

datediff("d",minimum({@buy,{table.assetcode}),maximum({@sell},{table.assetcode}))

I would use days (with 60 as your criterion) since datediff would should a month's difference if you compared 9/30/04 and 10/01/04, even though the difference is only a day.

-LB

 

[reidtw]

Hi lbass,

Many thanks for your post, but the grouping has to be by fund then security because more than one fund could hold the same security and the report is to show if a security is being sold within a period within funds not in a security as a whole. Therefore, will your formulae still work if the first group is the Fund and the second group the security?

Also, you say insert a min to the @Buy, max to the @Sell; sorry, possibly a stupid question, where should this be applied? In the format field option Common->Suppress?

T

 

[lbass]

Please use your terms consistently. Is AssetCode = Security? If so, you can use my suggestion. To insert a summary, you right click on the field or formula, in this case {@buy} or {@sell}, and choose insert summary->sum and select show for all groups and for the grand total (if you wish).

-LB

 

[reidtw]

Hi lbass

I've tried your suggestion but the datediff formula is returning zero in every instance, even where there is definitely a min buy and max sale for the same asset code.

I have put the @buy & @sell formulae in the details section, the min and max in grp footer 2, and datediff in the same footer. The asset code group is grp2, with a grp1 for the fund.

Any ideas what I'm doing wrong?

T

 

[lbass]

Please post the exact formulas you are using for {@buy},{@sell}, and datediff. On the surface, it looks like you might accidentally have used {@buy} or {@sell} for both dates in the datediff.

-LB

 

[reidtw]

Hi lbass

Here are the formulas: -

@BUY
if {Trade.BuySell} = "B" then {Trade.dtDeal}

@SELL
if {Trade.BuySell} = "S" then {Trade.dtDeal}

@DATEDIFF
datediff("d",minimum({@BUY},{Asset.sSecId}),maximum({@SELL},{Asset.sSecId}))

Cheers
T

 

[lbass]

Sorry, I led you astray. Change the buy and sell formulas to the following:

//{@buy}:
if isnull({Trade.BuySell}) or
{Trade.BuySell} <> "B" then
date(9999,09,09) else
{Trade.dtDeal}

//{@sell}:
if isnull({Trade.BuySell}) or
{Trade.BuySell} <> "S" then
date(1899,09,09) else
{Trade.dtDeal}

Create a difference formula for display:
//{@datediff}:
if maximum({@sell},{Asset.sSecId}) <> date(1899,09,09) then
datediff("d",minimum({@buy},{Asset.sSecId}),maximum({@sell},{Asset.sSecId})) else
if maximum({@sell},{Asset.sSecId}) = date(1899,09,09) then
datediff("d",minimum({@buy},{Asset.sSecId}),currentdate)

Then you can display those asset codes with a greater than 60 day difference by going to report->edit selection formula->GROUP and entering:

if maximum({@sell},{Asset.sSecId}) <> date(1899,09,09) then
datediff("d",minimum({@buy},{Asset.sSecId}),maximum({@sell},{Asset.sSecId})) > 60 else
if maximum({@sell},{Asset.sSecId}) = date(1899,09,09) then
datediff("d",minimum({@buy},{Asset.sSecId}),currentdate) > 60

If there is no sell date, but the buy date is more than 60 days ago, the group will display.

The earlier formula didn't work primarily because the minimum for {@buy} was a null. The formulas also didn't account for nulls for the sell date.

-LB

 

[reidtw]

Hi lbass,

Thanks very much for the latest code, it works perfectly!

I have made one adjustment as I need to display the min buy and max sell that are within the two months so I have changed the ">" signs in the group selection to "<".

Regards
T