Sorting Reports into Weeks Using Daily Data

[mcongdon]

I have database for stock trading. At the end of each day, the database creates a report of all the stock trades during that day (called a blotter), as well as totalling the commissions on those trades. The end of month report needs to be layed out in a fashion that each row is a business day of that month, and after the days in each week, there is a weekly total. At the end of the entire report, there needs to be the sum of all the weekly totals. I know I'll almost definitely have to use subreports, but I need to figure out a way to only include all the business days in a month on the report. Each year, they change, and this database is going to be around for a long time. I know this is difficult to understand so here is an idea of how the report should look:

Trade Date Commission
1-Jul 86.00
2-Jul 14.00
3-Jul 52.00
Total 152.00

7-Jul 123.00
8-Jul 46.00
9-Jul 86.00
10-Jul 20.00
11-Jul 5.00
Total 250.00

14-Jul
15-Jul
16-Jul
17-Jul
18-Jul
Total

21-Jul
22-Jul
23-Jul
24-Jul
25-Jul
Total

28-Jul
29-Jul
30-Jul
31-Jul
Total

Monthly Total: 402.00

The commissions are not stored anywhere, they are calculated using the information in a table (Price and Quantity)

Any ideas on how to group or sort data like this would be great. The problem I run into is when the month changes, the weeks have different dates in them. Some months have 31 days, others 30, and one 28. Of those months, the number of business days varies as well.

I cant figureout where to start.
Thanks!

 

[SoCalAccessPro]

That's gonna be a tough one, yeah.

I used to having a working days function, which would be my starting point in trying to create this report.

Google, or search here, for a working days function.

Once you get that, sorting and grouping will be fairly straight forward.

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[PWise]

A Very Simple Report

use this a the rowsource of the report

Select TradeDate ,(Price *Quantity)/commrate ,
year(TradeDate )*100)+datepart("ww",TradeDate ) TradeWeek,
month(TradeDate)Trademonth
from Tablename

group the report by TradeWeek and Trademonth

and in the group footer put a text box with this control source

=sum(commrate)

I dont see a need for working days function beacuse if it is not a working day there will be no trades you only need a working days function when you want to exclude days

 

[Remou]

This post discusses two approaches and includes a reference to a FAQ:

http://www.tek-tips.com/viewthread.cfm?qid=1170073

 

[PWise]

mcongdon:

if it is possable to have trades on weekend and or holidays

then create a holidaytable

holidaydate holidayname
1/1/8 new years
....
7/4/8 july 4th

Select TradeDate ,(Price *Quantity)/commrate ,
year(TradeDate )*100)+datepart("ww",TradeDate ) TradeWeek,
month(TradeDate)Trademonth
from Tradetable
left join holidaytable ht
on  ht.holidaydate =Tradetable.TradeDate 
where ht.holidaydate is null and datepart("w",TradeDate ) between 2 and 6

 

[mcongdon]

pwise,
I wasn't 100% sure on where to place the missing paretheses in the year function, so this is what I've come up with. Is it correct?

Select TradeDate, ((ClientPrice-FirmPrice)*Quantity), Year((TradeDate)*100)+datepart("ww",TradeDate) as TradeWeek, month(TradeDate) as TradeMonth, count(ClientPrice) as NumTickets
From tblBlotter

 

[PWise]

(year(TradeDate)*100)+datepart("ww",TradeDate)as TradeWeek