Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server time-weighted rate of return


TWRR

Updated: 19 October 2012


Use TWRR to calculate the time-weighted rate of return. Time-weighted rate of return is a portfolio measurement statistic which can be used when there are movements of cash into and out of the portfolio.
 
TWRR is calculated using the follow formula:

TWRR function for SQL Server - Time Weighted Rate of Return
 
Where
                r              is the time the time weighted rate of return
                n             is the period for which the return is calculated
                MV         is the ending market value for the period
                P             is the amount that has been added to the portfolio
                S              is the amount that has been subtracted from the portfolio
Syntax
SELECT [westclintech].[wct].[TWRR] (
  <@CF, float,>
 ,<@CFDate, datetime),>)
 ,<@MV, bit),>)
Arguments
@CF
the cash flow amounts. @CF is an expression of type float or of a type that can be implicitly converted to float.
@CFDate
the date on which the cash flow occurred. @CFDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
 
@MV
Identifies the cash flow as being the (ending) Market Value (‘True’). @MV is an expression of type bit or of a type that can be implicitly converted to bit.
Return Type
float
Remarks
·         The TWRR aggregate function requires a series of cash flows (@CF) and the dates on which those cash flows occurred (@CFDate) as input. As a result, the order of the cash flows is not important.
·         Dates on which the cash flow is zero, or on which there is no cash flow, do not have to be included.
·         Beginning market values and additions to the portfolio should be entered as cash flows where the amount is greater than zero.
·         Ending market values and withdrawals from the portfolio should be entered as cash flows where the amount is less than zero.
·         There is no requirement to enter beginning market values. If the beginning market value for period is zero, the beginning market value is assumed to be the ending market values from the previous period (which must be passed to the function).
·         There is no requirement to enter ending market values. If en ending market value is not entered for the day on which there is a cash movement, then the ending market value is obtained from the beginning market value for the next day for which there is a cash movement. If there is no cash movement for that day, then the ending market value is calculated as the beginning market values plus the purchases minus the sales.
·         Available in XLeratorDB / financial 2008 only
Examples
The following table summarizes the activity and market values for a portfolio for a month.

Date
BMV
Purchases
Sales
EMV
2012-01-03
15,000.00
436.49
0.00
15,477.26
2012-01-10
15,477.26
0.00
-495.97
15,115.74
2012-01-17
15,115.74
297.92
-609.07
14,878.10
2012-01-24
14,878.10
157.06
-880.28
14,197.25
2012-01-31
14,197.25
0.00
0.00
14,275.08

 
To calculate the TWRR on this portfolio we will create and populate a temporary table.
CREATE TABLE #t(
      trandate    datetime,
      trandescr   varchar(50),
      tranamt     float
      )
 
INSERT INTO #t VALUES ('2012-01-03','Beginning Market Value',15000)    
INSERT INTO #t VALUES ('2012-01-03','Ending Market Value',-15477.26)   
INSERT INTO #t VALUES ('2012-01-03','Purchases',436.49)    
INSERT INTO #t VALUES ('2012-01-03','Sales',0)
INSERT INTO #t VALUES ('2012-01-10','Ending Market Value',-15115.74)   
INSERT INTO #t VALUES ('2012-01-10','Purchases',0)   
INSERT INTO #t VALUES ('2012-01-10','Sales',-495.97)
INSERT INTO #t VALUES ('2012-01-17','Ending Market Value',-14878.1)    
INSERT INTO #t VALUES ('2012-01-17','Purchases',297.92)    
INSERT INTO #t VALUES ('2012-01-17','Sales',-609.07)
INSERT INTO #t VALUES ('2012-01-24','Ending Market Value',-14197.25)   
INSERT INTO #t VALUES ('2012-01-24','Purchases',157.06)    
INSERT INTO #t VALUES ('2012-01-24','Sales',-880.28)
INSERT INTO #t VALUES ('2012-01-31','Ending Market Value',-14275.08)   
INSERT INTO #t VALUES ('2012-01-31','Purchases',0)   
INSERT INTO #t VALUES ('2012-01-31','Sales',0)
We can then calculate the time weighted rate of return durectly by invoking the function.
SELECT wct.TWRR(tranamt
      ,trandate
      ,CASE trandescr
            WHEN 'Ending Market Value' THEN 'TRUE'
            ELSE 'FALSE'
       END
       ) as TWRR
FROM #t
This produces the following result
                  TWRR
----------------------
    0.0246243629116802
The time weighted return for the month of January, 2012 is approximately 2.46%. This figure means that the performance of this portfolio is the same as the perfromance of a portfolio which had an opening balance of 100 on 2012-01-03 and a closing balance of 102.46 on 2012-01-31, with no cash movements in the portfolio.
Notice that @MV was set to 'TRUE' only for the ending market values and that only the initial beginning market value was passed into the transaction. In this case, the function used the ending market value from the previous period as the beginning market value for the current period. 
Let’s look at slightly different presentation. Let’s assume that we actually have a table with this structure and we want to calculate the time-weighted rate of return.

Date
Purchases
Sales
EMV
2011-12-31
0.00
0.00
15,000.00
2012-01-03
436.49
0.00
15,477.26
2012-01-10
0.00
-495.97
15,115.74
2012-01-17
297.92
-609.07
14,878.10
2012-01-24
157.06
-880.28
14,197.25
2012-01-31
0.00
0.00
14,275.08

 
CREATE TABLE #t2(
      tdate datetime,
      purch money,
      sales money,
      emv         money
      )
 
INSERT INTO #t2 VALUES ('2011-12-31',0,0,15000.00)   
INSERT INTO #t2 VALUES ('2012-01-03',436.49,0,15477.26)    
INSERT INTO #t2 VALUES ('2012-01-10',0,495.97,15115.74)    
INSERT INTO #t2 VALUES ('2012-01-17',297.92,609.07,14878.10)     
INSERT INTO #t2 VALUES ('2012-01-24',157.06,880.28,14197.25)     
INSERT INTO #t2 VALUES ('2012-01-31',0,0,14275.08)   
We can then enter the following SQL to calculate the time-weighted rate of return.
SELECT wct.TWRR(cf,d,mv) as TWRR
FROM (
      SELECT tdate, purch, 'FALSE' from #t2
      UNION ALL
      SELECT tdate, -sales, 'FALSE' from #t2
      UNION ALL
      SELECT tdate, -emv, 'TRUE' from #t2
      ) n(d, cf, mv)
This produces the following result.
                  TWRR
----------------------
    0.0246243629116802
Given the following information, calculate the time –weighted rate of return for the portfolio for the first quarter.

Date
Market Value
Cash Flow
Market Value Post Cash Flow
2012-Dec-31
500,000
 
 
2013-Jan-31
509,000
 
 
2013-Feb-19
513,000
+50,000
563,000
2013-Feb-28
575,000
 
 
2013-Mar-12
585,000
-20,000
575,000
2013-Mar-31
570,000
 
 

 We could enter the following SQL to perform the calculation.
SELECT wct.TWRR(cf,cfdate,mv) as TWRR
FROM (VALUES
      (-500000,'2012-12-31','True'),
      (-509000,'2013-01-31','True'),
      (513000,'2013-02-19','True'),
      (50000,'2013-02-19','False'),
      (-575000,'2013-02-28','True'),
      (585000,'2013-03-12','True'),
      (-20000,'2013-03-12','False'),
      (-570000,'2013-03-31','True')
      )n(cf,cfdate,mv)
This produces the following result.
                  TWRR
----------------------
    0.0755268080290477
Note, that we did not include the ending market values for 2013-Feb-19 and 2013-Mar-12, but only because the ending market value was equal to the market value plus the cash flow. Here’s the SQL with those ending market values included, which produces exactly the same result.
SELECT wct.TWRR(cf,cfdate,mv) as TWRR
FROM (VALUES
      (-500000,'2012-12-31','True'),
      (-509000,'2013-01-31','True'),
      (513000,'2013-02-19','True'),
      (50000,'2013-02-19','False'),
      (-575000,'2013-02-28','True'),
      (585000,'2013-03-12','True'),
      (-20000,'2013-03-12','False'),
      (-570000,'2013-03-31','True'),
      (-563000,'2013-02-19','True'),
      (-565000,'2013-03-12','True')
      )n(cf,cfdate,mv)
This produces the following result.
                  TWRR
----------------------
    0.0755268080290477
Let’s look at another example, where we have a transaction table, containing all the purchase and sale transactions, and a market value table, which holds the ending market value for each day. We will continue to assume that all the transactions are for a single account or portfolio.
/*A transaction table*/
CREATE TABLE #t3(
      TranNo      float,
      TranDate    datetime,
      TranAmt     money,
      PS          char(1)
      )
/*Insert rows into the transaction table*/
INSERT INTO #t3 VALUES (1,'2012-01-24',443.03,'S')
INSERT INTO #t3 VALUES (2,'2012-01-04',206.68,'P')
INSERT INTO #t3 VALUES (3,'2012-01-30',230.45,'S')
INSERT INTO #t3 VALUES (4,'2012-01-21',123.94,'P')
INSERT INTO #t3 VALUES (5,'2012-01-24',18.41,'P')
INSERT INTO #t3 VALUES (6,'2012-01-11',323.42,'P')
INSERT INTO #t3 VALUES (7,'2012-01-06',321.79,'P')
INSERT INTO #t3 VALUES (8,'2012-01-30',141.03,'P')
INSERT INTO #t3 VALUES (9,'2012-01-06',454.14,'P')
INSERT INTO #t3 VALUES (10,'2012-01-30',495.57,'S')
INSERT INTO #t3 VALUES (11,'2012-01-25',77.56,'P')
INSERT INTO #t3 VALUES (12,'2012-01-27',94.72,'P')
INSERT INTO #t3 VALUES (13,'2012-01-05',331.05,'P')
INSERT INTO #t3 VALUES (14,'2012-01-26',382.78,'S')
INSERT INTO #t3 VALUES (15,'2012-01-25',399.07,'S')
INSERT INTO #t3 VALUES (16,'2012-01-13',49.72,'S')
INSERT INTO #t3 VALUES (17,'2012-01-15',159.08,'S')
INSERT INTO #t3 VALUES (18,'2012-01-16',494.56,'P')
INSERT INTO #t3 VALUES (19,'2012-01-30',442.46,'P')
INSERT INTO #t3 VALUES (20,'2012-01-30',326.42,'P')
INSERT INTO #t3 VALUES (21,'2012-01-13',54.31,'P')
INSERT INTO #t3 VALUES (22,'2012-01-04',448.36,'S')
INSERT INTO #t3 VALUES (23,'2012-01-26',491.41,'P')
INSERT INTO #t3 VALUES (24,'2012-01-28',110.53,'S')
INSERT INTO #t3 VALUES (25,'2012-01-17',255.22,'P')
INSERT INTO #t3 VALUES (26,'2012-01-19',386.82,'P')
INSERT INTO #t3 VALUES (27,'2012-01-08',334.43,'S')
INSERT INTO #t3 VALUES (28,'2012-01-03',434.05,'P')
INSERT INTO #t3 VALUES (29,'2012-01-16',213.43,'P')
INSERT INTO #t3 VALUES (30,'2012-01-16',377.25,'P')
INSERT INTO #t3 VALUES (31,'2012-01-30',369.98,'S')
INSERT INTO #t3 VALUES (32,'2012-01-28',79.15,'S')
INSERT INTO #t3 VALUES (33,'2012-01-30',7.59,'S')
INSERT INTO #t3 VALUES (34,'2012-01-25',375.2,'P')
INSERT INTO #t3 VALUES (35,'2012-01-19',457.77,'S')
INSERT INTO #t3 VALUES (36,'2012-01-09',133.37,'P')
INSERT INTO #t3 VALUES (37,'2012-01-09',348.05,'S')
INSERT INTO #t3 VALUES (38,'2012-01-26',102.9,'P')
INSERT INTO #t3 VALUES (39,'2012-01-28',327.83,'S')
INSERT INTO #t3 VALUES (40,'2012-01-26',430.54,'S')
INSERT INTO #t3 VALUES (41,'2012-01-15',295.55,'P')
INSERT INTO #t3 VALUES (42,'2012-01-22',287.58,'S')
INSERT INTO #t3 VALUES (43,'2012-01-24',177,'P')
INSERT INTO #t3 VALUES (44,'2012-01-22',244.55,'S')
INSERT INTO #t3 VALUES (45,'2012-01-19',103.37,'P')
INSERT INTO #t3 VALUES (46,'2012-01-05',30.41,'P')
INSERT INTO #t3 VALUES (47,'2012-01-03',198.96,'P')
INSERT INTO #t3 VALUES (48,'2012-01-20',133.89,'P')
INSERT INTO #t3 VALUES (49,'2012-01-17',279.8,'S')
INSERT INTO #t3 VALUES (50,'2012-01-04',303.53,'P')
INSERT INTO #t3 VALUES (51,'2012-01-12',19.03,'P')
INSERT INTO #t3 VALUES (52,'2012-01-03',43.41,'S')
INSERT INTO #t3 VALUES (53,'2012-01-10',153.24,'S')
INSERT INTO #t3 VALUES (54,'2012-01-26',355.44,'P')
INSERT INTO #t3 VALUES (55,'2012-01-23',498.07,'P')
INSERT INTO #t3 VALUES (56,'2012-01-28',408.11,'P')
INSERT INTO #t3 VALUES (57,'2012-01-18',158.1,'S')
INSERT INTO #t3 VALUES (58,'2012-01-23',135.44,'S')
INSERT INTO #t3 VALUES (59,'2012-01-20',118.38,'S')
INSERT INTO #t3 VALUES (60,'2012-01-16',234.17,'P')
INSERT INTO #t3 VALUES (61,'2012-01-08',131.35,'P')
INSERT INTO #t3 VALUES (62,'2012-01-11',107.71,'P')
INSERT INTO #t3 VALUES (63,'2012-01-11',48.28,'P')
INSERT INTO #t3 VALUES (64,'2012-01-10',42.11,'P')
INSERT INTO #t3 VALUES (65,'2012-01-14',395.03,'P')
INSERT INTO #t3 VALUES (66,'2012-01-11',173.67,'S')
INSERT INTO #t3 VALUES (67,'2012-01-21',98.8,'P')
INSERT INTO #t3 VALUES (68,'2012-01-16',452.9,'P')
INSERT INTO #t3 VALUES (69,'2012-01-22',468.29,'S')
INSERT INTO #t3 VALUES (70,'2012-01-12',339.25,'S')
INSERT INTO #t3 VALUES (71,'2012-01-05',165.75,'P')
INSERT INTO #t3 VALUES (72,'2012-01-13',403.86,'S')
INSERT INTO #t3 VALUES (73,'2012-01-22',86.25,'P')
INSERT INTO #t3 VALUES (74,'2012-01-04',389.18,'S')
INSERT INTO #t3 VALUES (75,'2012-01-20',486.33,'S')
INSERT INTO #t3 VALUES (76,'2012-01-16',234.62,'P')
INSERT INTO #t3 VALUES (77,'2012-01-07',339.63,'S')
INSERT INTO #t3 VALUES (78,'2012-01-14',124.5,'P')
INSERT INTO #t3 VALUES (79,'2012-01-11',98.72,'P')
INSERT INTO #t3 VALUES (80,'2012-01-06',347.27,'P')
INSERT INTO #t3 VALUES (81,'2012-01-07',487.62,'P')
INSERT INTO #t3 VALUES (82,'2012-01-08',280.18,'P')
INSERT INTO #t3 VALUES (83,'2012-01-22',205.78,'S')
INSERT INTO #t3 VALUES (84,'2012-01-09',154.41,'S')
INSERT INTO #t3 VALUES (85,'2012-01-22',11.84,'S')
INSERT INTO #t3 VALUES (86,'2012-01-07',464.59,'P')
INSERT INTO #t3 VALUES (87,'2012-01-20',323.95,'S')
INSERT INTO #t3 VALUES (88,'2012-01-26',131.63,'S')
INSERT INTO #t3 VALUES (89,'2012-01-16',32.9,'S')
INSERT INTO #t3 VALUES (90,'2012-01-03',404.52,'P')
INSERT INTO #t3 VALUES (91,'2012-01-24',420.44,'S')
INSERT INTO #t3 VALUES (92,'2012-01-12',468.48,'P')
INSERT INTO #t3 VALUES (93,'2012-01-03',482.73,'P')
INSERT INTO #t3 VALUES (94,'2012-01-22',72.64,'S')
INSERT INTO #t3 VALUES (95,'2012-01-22',15.38,'S')
INSERT INTO #t3 VALUES (96,'2012-01-05',413.72,'P')
INSERT INTO #t3 VALUES (97,'2012-01-12',217.6,'S')
INSERT INTO #t3 VALUES (98,'2012-01-31',317.93,'P')
INSERT INTO #t3 VALUES (99,'2012-01-04',68.58,'S')
INSERT INTO #t3 VALUES (100,'2012-01-08',330.72,'S')
INSERT INTO #t3 VALUES (101,'2012-01-25',45.26,'S')
/*A market value table*/
CREATE TABLE #mv(
      trandate    datetime,
      emv         money
      )
/*Insert Rows into the market value table*/
INSERT INTO #mv VALUES ('2011-12-31',15000)
INSERT INTO #mv VALUES ('2012-01-03',16566.31)
INSERT INTO #mv VALUES ('2012-01-04',16222.72)
INSERT INTO #mv VALUES ('2012-01-05',17058.02)
INSERT INTO #mv VALUES ('2012-01-06',18213.62)
INSERT INTO #mv VALUES ('2012-01-07',18698.79)
INSERT INTO #mv VALUES ('2012-01-08',18357.06)
INSERT INTO #mv VALUES ('2012-01-09',17972.14)
INSERT INTO #mv VALUES ('2012-01-10',17708.68)
INSERT INTO #mv VALUES ('2012-01-11',17944.47)
INSERT INTO #mv VALUES ('2012-01-12',17745.1)
INSERT INTO #mv VALUES ('2012-01-13',17317.47)
INSERT INTO #mv VALUES ('2012-01-14',17765.5)
INSERT INTO #mv VALUES ('2012-01-15',18051.69)
INSERT INTO #mv VALUES ('2012-01-16',20167.64)
INSERT INTO #mv VALUES ('2012-01-17',20229.2)
INSERT INTO #mv VALUES ('2012-01-18',20114.92)
INSERT INTO #mv VALUES ('2012-01-19',20117.91)
INSERT INTO #mv VALUES ('2012-01-20',19376.21)
INSERT INTO #mv VALUES ('2012-01-21',19518.13)
INSERT INTO #mv VALUES ('2012-01-22',18170.05)
INSERT INTO #mv VALUES ('2012-01-23',18502.23)
INSERT INTO #mv VALUES ('2012-01-24',17736.57)
INSERT INTO #mv VALUES ('2012-01-25',17882.04)
INSERT INTO #mv VALUES ('2012-01-26',17988.62)
INSERT INTO #mv VALUES ('2012-01-27',17934.44)
INSERT INTO #mv VALUES ('2012-01-28',17730.44)
INSERT INTO #mv VALUES ('2012-01-30',17661.15)
INSERT INTO #mv VALUES ('2012-01-31',17901.49)
/*Calculate the time-weighted rate of return*/
SELECT wct.TWRR(c,d,mv) as TWRR
FROM (
      SELECT Trandate
      ,CASE PS   
            WHEN 'P' THEN TranAmt
            ELSE -TranAmt
       END
      ,'False'
      FROM #t3
      UNION ALL
      SELECT Trandate
      ,-emv
      ,'True'
      FROM #mv
      ) n(d, c, mv)
This produces that following result.
                  TWRR
----------------------
   -0.0309539334921339
 
Finally, lets look at an example where we have many purchase and sale transactions across many accounts. As in the previous example, we will have the transactions and the market values in separate tables.
/*A transaction table*/
CREATE TABLE #t4(
      Account           float,
      TranNo            float,
      TranDate    datetime,
      TranAmt           money,
      PS                char(1)
      )
/*Insert rows into the transaction table*/
INSERT INTO #t4 VALUES (5,1,'2012-01-07',193,'P')
INSERT INTO #t4 VALUES (4,2,'2012-01-03',433.94,'S')
INSERT INTO #t4 VALUES (7,3,'2012-01-27',375.67,'P')
INSERT INTO #t4 VALUES (4,4,'2012-01-29',259.3,'P')
INSERT INTO #t4 VALUES (5,5,'2012-01-20',265.77,'P')
INSERT INTO #t4 VALUES (2,6,'2012-01-16',140.77,'P')
INSERT INTO #t4 VALUES (5,7,'2012-01-04',331.01,'P')
INSERT INTO #t4 VALUES (7,8,'2012-01-16',192.06,'S')
INSERT INTO #t4 VALUES (8,9,'2012-01-21',181.18,'S')
INSERT INTO #t4 VALUES (6,10,'2012-01-17',283.65,'P')
INSERT INTO #t4 VALUES (1,11,'2012-01-18',238.66,'S')
INSERT INTO #t4 VALUES (10,12,'2012-01-14',454.69,'P')
INSERT INTO #t4 VALUES (4,13,'2012-01-13',49.13,'P')
INSERT INTO #t4 VALUES (2,14,'2012-01-25',393.34,'P')
INSERT INTO #t4 VALUES (10,15,'2012-01-17',280.07,'S')
INSERT INTO #t4 VALUES (4,16,'2012-01-07',278.37,'P')
INSERT INTO #t4 VALUES (5,17,'2012-01-23',150.21,'S')
INSERT INTO #t4 VALUES (3,18,'2012-01-24',157.86,'S')
INSERT INTO #t4 VALUES (8,19,'2012-01-06',420.21,'P')
INSERT INTO #t4 VALUES (6,20,'2012-01-05',416.18,'P')
INSERT INTO #t4 VALUES (1,21,'2012-01-27',177.74,'S')
INSERT INTO #t4 VALUES (2,22,'2012-01-05',112.99,'P')
INSERT INTO #t4 VALUES (3,23,'2012-01-21',213.14,'P')
INSERT INTO #t4 VALUES (4,24,'2012-01-10',26.14,'P')
INSERT INTO #t4 VALUES (2,25,'2012-01-21',403.89,'S')
INSERT INTO #t4 VALUES (6,26,'2012-01-05',402.07,'P')
INSERT INTO #t4 VALUES (7,27,'2012-01-26',172.23,'P')
INSERT INTO #t4 VALUES (10,28,'2012-01-06',57.14,'S')
INSERT INTO #t4 VALUES (6,29,'2012-01-24',456.4,'P')
INSERT INTO #t4 VALUES (7,30,'2012-01-03',82.61,'P')
INSERT INTO #t4 VALUES (5,31,'2012-01-23',312.75,'S')
INSERT INTO #t4 VALUES (3,32,'2012-01-31',324.15,'P')
INSERT INTO #t4 VALUES (4,33,'2012-01-15',425.61,'P')
INSERT INTO #t4 VALUES (4,34,'2012-01-10',26.97,'S')
INSERT INTO #t4 VALUES (2,35,'2012-01-16',13.58,'S')
INSERT INTO #t4 VALUES (2,36,'2012-01-22',359.44,'P')
INSERT INTO #t4 VALUES (2,37,'2012-01-16',169.76,'P')
INSERT INTO #t4 VALUES (2,38,'2012-01-07',185.33,'P')
INSERT INTO #t4 VALUES (10,39,'2012-01-26',308.47,'P')
INSERT INTO #t4 VALUES (10,40,'2012-01-20',259.13,'P')
INSERT INTO #t4 VALUES (1,41,'2012-01-11',170.75,'S')
INSERT INTO #t4 VALUES (1,42,'2012-01-30',289.28,'S')
INSERT INTO #t4 VALUES (7,43,'2012-01-11',479.04,'P')
INSERT INTO #t4 VALUES (7,44,'2012-01-17',414.43,'S')
INSERT INTO #t4 VALUES (1,45,'2012-01-21',426.68,'P')
INSERT INTO #t4 VALUES (1,46,'2012-01-29',409.32,'S')
INSERT INTO #t4 VALUES (5,47,'2012-01-26',482.93,'P')
INSERT INTO #t4 VALUES (6,48,'2012-01-21',34.37,'S')
INSERT INTO #t4 VALUES (9,49,'2012-01-25',413.87,'S')
INSERT INTO #t4 VALUES (5,50,'2012-01-25',433.75,'S')
INSERT INTO #t4 VALUES (1,51,'2012-01-24',86.32,'S')
INSERT INTO #t4 VALUES (2,52,'2012-01-03',299.93,'S')
INSERT INTO #t4 VALUES (3,53,'2012-01-29',313.76,'P')
INSERT INTO #t4 VALUES (10,54,'2012-01-11',221.75,'S')
INSERT INTO #t4 VALUES (10,55,'2012-01-22',217.94,'S')
INSERT INTO #t4 VALUES (3,56,'2012-01-15',0.89,'P')
INSERT INTO #t4 VALUES (1,57,'2012-01-09',301.44,'P')
INSERT INTO #t4 VALUES (2,58,'2012-01-18',479.71,'S')
INSERT INTO #t4 VALUES (8,59,'2012-01-21',217.12,'P')
INSERT INTO #t4 VALUES (3,60,'2012-01-04',310.77,'P')
INSERT INTO #t4 VALUES (8,61,'2012-01-12',82.97,'S')
INSERT INTO #t4 VALUES (9,62,'2012-01-03',337.39,'P')
INSERT INTO #t4 VALUES (5,63,'2012-01-11',300.44,'P')
INSERT INTO #t4 VALUES (3,64,'2012-01-17',181.82,'P')
INSERT INTO #t4 VALUES (3,65,'2012-01-23',160.31,'P')
INSERT INTO #t4 VALUES (1,66,'2012-01-11',370.76,'P')
INSERT INTO #t4 VALUES (6,67,'2012-01-15',485.3,'P')
INSERT INTO #t4 VALUES (7,68,'2012-01-23',359.27,'P')
INSERT INTO #t4 VALUES (9,69,'2012-01-29',435.07,'S')
INSERT INTO #t4 VALUES (8,70,'2012-01-11',172.44,'P')
INSERT INTO #t4 VALUES (6,71,'2012-01-15',387.01,'P')
INSERT INTO #t4 VALUES (3,72,'2012-01-21',451.6,'P')
INSERT INTO #t4 VALUES (8,73,'2012-01-20',447.41,'S')
INSERT INTO #t4 VALUES (2,74,'2012-01-19',239.09,'P')
INSERT INTO #t4 VALUES (4,75,'2012-01-08',31.82,'S')
INSERT INTO #t4 VALUES (2,76,'2012-01-24',0.5,'P')
INSERT INTO #t4 VALUES (9,77,'2012-01-06',368.34,'S')
INSERT INTO #t4 VALUES (8,78,'2012-01-13',373.48,'S')
INSERT INTO #t4 VALUES (10,79,'2012-01-08',145.68,'P')
INSERT INTO #t4 VALUES (8,80,'2012-01-14',421.34,'P')
INSERT INTO #t4 VALUES (3,81,'2012-01-27',426.32,'S')
INSERT INTO #t4 VALUES (6,82,'2012-01-19',498.18,'P')
INSERT INTO #t4 VALUES (1,83,'2012-01-28',227.59,'P')
INSERT INTO #t4 VALUES (2,84,'2012-01-22',132.22,'P')
INSERT INTO #t4 VALUES (5,85,'2012-01-28',199.96,'S')
INSERT INTO #t4 VALUES (2,86,'2012-01-22',128.54,'S')
INSERT INTO #t4 VALUES (2,87,'2012-01-08',54.64,'S')
INSERT INTO #t4 VALUES (10,88,'2012-01-21',325.61,'S')
INSERT INTO #t4 VALUES (7,89,'2012-01-14',97.29,'P')
INSERT INTO #t4 VALUES (6,90,'2012-01-26',280.88,'S')
INSERT INTO #t4 VALUES (6,91,'2012-01-05',343.55,'S')
INSERT INTO #t4 VALUES (2,92,'2012-01-22',401.53,'P')
INSERT INTO #t4 VALUES (4,93,'2012-01-23',3.8,'P')
INSERT INTO #t4 VALUES (5,94,'2012-01-13',216.94,'S')
INSERT INTO #t4 VALUES (9,95,'2012-01-09',137.85,'S')
INSERT INTO #t4 VALUES (6,96,'2012-01-23',252.26,'P')
INSERT INTO #t4 VALUES (1,97,'2012-01-29',413.12,'S')
INSERT INTO #t4 VALUES (2,98,'2012-01-11',156.34,'S')
INSERT INTO #t4 VALUES (5,99,'2012-01-16',471.37,'P')
INSERT INTO #t4 VALUES (1,100,'2012-01-13',31.22,'P')
INSERT INTO #t4 VALUES (3,101,'2012-01-04',190.55,'P')
INSERT INTO #t4 VALUES (2,102,'2012-01-19',220.66,'S')
INSERT INTO #t4 VALUES (2,103,'2012-01-16',361.22,'P')
INSERT INTO #t4 VALUES (6,104,'2012-01-16',11.26,'S')
INSERT INTO #t4 VALUES (1,105,'2012-01-12',493.1,'S')
INSERT INTO #t4 VALUES (2,106,'2012-01-08',120.85,'S')
INSERT INTO #t4 VALUES (2,107,'2012-01-06',469.36,'S')
INSERT INTO #t4 VALUES (2,108,'2012-01-19',95.96,'P')
INSERT INTO #t4 VALUES (3,109,'2012-01-09',140.68,'P')
INSERT INTO #t4 VALUES (10,110,'2012-01-10',300.67,'P')
INSERT INTO #t4 VALUES (3,111,'2012-01-11',234.91,'S')
INSERT INTO #t4 VALUES (3,112,'2012-01-06',84.08,'S')
INSERT INTO #t4 VALUES (2,113,'2012-01-23',72.69,'P')
INSERT INTO #t4 VALUES (3,114,'2012-01-17',95.02,'S')
INSERT INTO #t4 VALUES (8,115,'2012-01-21',186.21,'P')
INSERT INTO #t4 VALUES (3,116,'2012-01-22',142.4,'P')
INSERT INTO #t4 VALUES (2,117,'2012-01-27',29.39,'S')
INSERT INTO #t4 VALUES (1,118,'2012-01-28',294.85,'S')
INSERT INTO #t4 VALUES (7,119,'2012-01-25',419.66,'S')
INSERT INTO #t4 VALUES (9,120,'2012-01-16',198.61,'S')
INSERT INTO #t4 VALUES (7,121,'2012-01-20',169.37,'P')
INSERT INTO #t4 VALUES (2,122,'2012-01-21',491.1,'P')
INSERT INTO #t4 VALUES (1,123,'2012-01-08',420.79,'S')
INSERT INTO #t4 VALUES (8,124,'2012-01-24',79.3,'S')
INSERT INTO #t4 VALUES (7,125,'2012-01-05',318.8,'P')
INSERT INTO #t4 VALUES (2,126,'2012-01-16',7.31,'S')
INSERT INTO #t4 VALUES (6,127,'2012-01-14',171.45,'P')
INSERT INTO #t4 VALUES (3,128,'2012-01-22',425.94,'S')
INSERT INTO #t4 VALUES (1,129,'2012-01-16',22.1,'P')
INSERT INTO #t4 VALUES (6,130,'2012-01-05',105.81,'P')
INSERT INTO #t4 VALUES (9,131,'2012-01-28',450.53,'P')
INSERT INTO #t4 VALUES (1,132,'2012-01-15',94.24,'S')
INSERT INTO #t4 VALUES (5,133,'2012-01-20',365.81,'P')
INSERT INTO #t4 VALUES (1,134,'2012-01-24',250.44,'P')
INSERT INTO #t4 VALUES (3,135,'2012-01-23',174.05,'S')
INSERT INTO #t4 VALUES (3,136,'2012-01-11',364.29,'P')
INSERT INTO #t4 VALUES (3,137,'2012-01-09',337.16,'S')
INSERT INTO #t4 VALUES (9,138,'2012-01-24',30.75,'S')
INSERT INTO #t4 VALUES (9,139,'2012-01-24',173.39,'S')
INSERT INTO #t4 VALUES (8,140,'2012-01-26',280.12,'P')
INSERT INTO #t4 VALUES (4,141,'2012-01-27',85.37,'P')
INSERT INTO #t4 VALUES (1,142,'2012-01-14',52.67,'S')
INSERT INTO #t4 VALUES (9,143,'2012-01-04',72.56,'P')
INSERT INTO #t4 VALUES (7,144,'2012-01-09',86.86,'P')
INSERT INTO #t4 VALUES (10,145,'2012-01-03',487.51,'S')
INSERT INTO #t4 VALUES (9,146,'2012-01-21',433.26,'P')
INSERT INTO #t4 VALUES (9,147,'2012-01-21',139.6,'P')
INSERT INTO #t4 VALUES (7,148,'2012-01-12',8.85,'P')
INSERT INTO #t4 VALUES (9,149,'2012-01-15',331.08,'P')
INSERT INTO #t4 VALUES (4,150,'2012-01-22',138.73,'P')
INSERT INTO #t4 VALUES (1,151,'2012-01-07',292.69,'S')
INSERT INTO #t4 VALUES (2,152,'2012-01-12',166.4,'P')
INSERT INTO #t4 VALUES (9,153,'2012-01-08',95.82,'P')
INSERT INTO #t4 VALUES (8,154,'2012-01-26',467.95,'S')
INSERT INTO #t4 VALUES (7,155,'2012-01-04',467.55,'P')
INSERT INTO #t4 VALUES (10,156,'2012-01-24',157.61,'S')
INSERT INTO #t4 VALUES (10,157,'2012-01-13',301.34,'S')
INSERT INTO #t4 VALUES (4,158,'2012-01-04',282.53,'P')
INSERT INTO #t4 VALUES (1,159,'2012-01-23',11.11,'P')
INSERT INTO #t4 VALUES (2,160,'2012-01-08',298.94,'P')
INSERT INTO #t4 VALUES (8,161,'2012-01-29',385.65,'P')
INSERT INTO #t4 VALUES (4,162,'2012-01-25',326.45,'P')
INSERT INTO #t4 VALUES (3,163,'2012-01-12',113.98,'P')
INSERT INTO #t4 VALUES (10,164,'2012-01-24',361.17,'P')
INSERT INTO #t4 VALUES (2,165,'2012-01-19',211.64,'S')
INSERT INTO #t4 VALUES (9,166,'2012-01-05',244.2,'P')
INSERT INTO #t4 VALUES (10,167,'2012-01-18',110.39,'P')
INSERT INTO #t4 VALUES (8,168,'2012-01-20',297.8,'P')
INSERT INTO #t4 VALUES (10,169,'2012-01-11',295.05,'P')
INSERT INTO #t4 VALUES (7,170,'2012-01-28',299.23,'P')
INSERT INTO #t4 VALUES (3,171,'2012-01-25',203.88,'P')
INSERT INTO #t4 VALUES (6,172,'2012-01-28',115.1,'P')
INSERT INTO #t4 VALUES (7,173,'2012-01-04',425.18,'S')
INSERT INTO #t4 VALUES (1,174,'2012-01-05',63.76,'S')
INSERT INTO #t4 VALUES (2,175,'2012-01-31',120.18,'S')
INSERT INTO #t4 VALUES (8,176,'2012-01-27',412.1,'P')
INSERT INTO #t4 VALUES (9,177,'2012-01-14',442.39,'P')
INSERT INTO #t4 VALUES (8,178,'2012-01-03',350.19,'P')
INSERT INTO #t4 VALUES (2,179,'2012-01-15',320,'P')
INSERT INTO #t4 VALUES (7,180,'2012-01-09',425.41,'P')
INSERT INTO #t4 VALUES (6,181,'2012-01-08',466.04,'S')
INSERT INTO #t4 VALUES (3,182,'2012-01-11',100.41,'P')
INSERT INTO #t4 VALUES (5,183,'2012-01-13',260.95,'S')
INSERT INTO #t4 VALUES (8,184,'2012-01-05',74.4,'S')
INSERT INTO #t4 VALUES (7,185,'2012-01-15',14.07,'P')
INSERT INTO #t4 VALUES (7,186,'2012-01-22',340.1,'S')
INSERT INTO #t4 VALUES (7,187,'2012-01-21',216.19,'S')
INSERT INTO #t4 VALUES (5,188,'2012-01-11',201.37,'P')
INSERT INTO #t4 VALUES (1,189,'2012-01-29',73.77,'S')
INSERT INTO #t4 VALUES (5,190,'2012-01-26',133.93,'S')
INSERT INTO #t4 VALUES (3,191,'2012-01-03',495.33,'S')
INSERT INTO #t4 VALUES (2,192,'2012-01-18',425.13,'S')
INSERT INTO #t4 VALUES (8,193,'2012-01-30',396.04,'S')
INSERT INTO #t4 VALUES (5,194,'2012-01-11',148.99,'P')
INSERT INTO #t4 VALUES (7,195,'2012-01-31',70.03,'P')
INSERT INTO #t4 VALUES (9,196,'2012-01-30',205.96,'P')
INSERT INTO #t4 VALUES (3,197,'2012-01-25',492.73,'S')
INSERT INTO #t4 VALUES (3,198,'2012-01-22',483.12,'S')
INSERT INTO #t4 VALUES (10,199,'2012-01-26',274.27,'S')
INSERT INTO #t4 VALUES (3,200,'2012-01-10',343.14,'P')
/*A market value table*/
CREATE TABLE #mv(
      Account           float,
      trandate    datetime,
      emv               money
      )
/*Insert Rows into the market value table*/
INSERT INTO #mv VALUES (1,'2011-12-31',15643.93)
INSERT INTO #mv VALUES (1,'2012-01-05',15712.93)
INSERT INTO #mv VALUES (1,'2012-01-07',15436.12)
INSERT INTO #mv VALUES (1,'2012-01-08',15020.11)
INSERT INTO #mv VALUES (1,'2012-01-09',15359.63)
INSERT INTO #mv VALUES (1,'2012-01-11',15608.22)
INSERT INTO #mv VALUES (1,'2012-01-12',15196.35)
INSERT INTO #mv VALUES (1,'2012-01-13',15265.86)
INSERT INTO #mv VALUES (1,'2012-01-14',15324.42)
INSERT INTO #mv VALUES (1,'2012-01-15',15234.79)
INSERT INTO #mv VALUES (1,'2012-01-16',15345.13)
INSERT INTO #mv VALUES (1,'2012-01-18',15219.45)
INSERT INTO #mv VALUES (1,'2012-01-21',15789.64)
INSERT INTO #mv VALUES (1,'2012-01-23',15827.54)
INSERT INTO #mv VALUES (1,'2012-01-24',16140.84)
INSERT INTO #mv VALUES (1,'2012-01-27',16080.08)
INSERT INTO #mv VALUES (1,'2012-01-28',16050.71)
INSERT INTO #mv VALUES (1,'2012-01-29',15292.18)
INSERT INTO #mv VALUES (1,'2012-01-30',15037.89)
INSERT INTO #mv VALUES (1,'2012-01-31',15172.65)
INSERT INTO #mv VALUES (2,'2011-12-31',14886.95)
INSERT INTO #mv VALUES (2,'2012-01-03',14684.51)
INSERT INTO #mv VALUES (2,'2012-01-05',14819.27)
INSERT INTO #mv VALUES (2,'2012-01-06',14374.05)
INSERT INTO #mv VALUES (2,'2012-01-07',14664.25)
INSERT INTO #mv VALUES (2,'2012-01-08',14911.39)
INSERT INTO #mv VALUES (2,'2012-01-11',14801.46)
INSERT INTO #mv VALUES (2,'2012-01-12',14999.48)
INSERT INTO #mv VALUES (2,'2012-01-15',15369.01)
INSERT INTO #mv VALUES (2,'2012-01-16',16035.66)
INSERT INTO #mv VALUES (2,'2012-01-18',15166.81)
INSERT INTO #mv VALUES (2,'2012-01-19',15189)
INSERT INTO #mv VALUES (2,'2012-01-21',15341.51)
INSERT INTO #mv VALUES (2,'2012-01-22',16161.51)
INSERT INTO #mv VALUES (2,'2012-01-23',16336.81)
INSERT INTO #mv VALUES (2,'2012-01-24',16398.69)
INSERT INTO #mv VALUES (2,'2012-01-25',16815.77)
INSERT INTO #mv VALUES (2,'2012-01-27',16902.22)
INSERT INTO #mv VALUES (2,'2012-01-31',16848.31)
INSERT INTO #mv VALUES (3,'2011-12-31',14754.84)
INSERT INTO #mv VALUES (3,'2012-01-03',14369.97)
INSERT INTO #mv VALUES (3,'2012-01-04',14991.02)
INSERT INTO #mv VALUES (3,'2012-01-06',15014.21)
INSERT INTO #mv VALUES (3,'2012-01-09',14867.73)
INSERT INTO #mv VALUES (3,'2012-01-10',15308.52)
INSERT INTO #mv VALUES (3,'2012-01-11',15539.35)
INSERT INTO #mv VALUES (3,'2012-01-12',15749.06)
INSERT INTO #mv VALUES (3,'2012-01-15',15799.77)
INSERT INTO #mv VALUES (3,'2012-01-17',15990.04)
INSERT INTO #mv VALUES (3,'2012-01-21',16746.61)
INSERT INTO #mv VALUES (3,'2012-01-22',15993.81)
INSERT INTO #mv VALUES (3,'2012-01-23',16054.78)
INSERT INTO #mv VALUES (3,'2012-01-24',16027.69)
INSERT INTO #mv VALUES (3,'2012-01-25',15865.42)
INSERT INTO #mv VALUES (3,'2012-01-27',15535.24)
INSERT INTO #mv VALUES (3,'2012-01-29',15858.26)
INSERT INTO #mv VALUES (3,'2012-01-31',16234.09)
INSERT INTO #mv VALUES (4,'2011-12-31',14883.52)
INSERT INTO #mv VALUES (4,'2012-01-03',14507.26)
INSERT INTO #mv VALUES (4,'2012-01-04',14871.47)
INSERT INTO #mv VALUES (4,'2012-01-07',15279.96)
INSERT INTO #mv VALUES (4,'2012-01-08',15347.63)
INSERT INTO #mv VALUES (4,'2012-01-10',15369.4)
INSERT INTO #mv VALUES (4,'2012-01-13',15489.99)
INSERT INTO #mv VALUES (4,'2012-01-15',16004.41)
INSERT INTO #mv VALUES (4,'2012-01-22',16284.76)
INSERT INTO #mv VALUES (4,'2012-01-23',16370.82)
INSERT INTO #mv VALUES (4,'2012-01-25',16810.76)
INSERT INTO #mv VALUES (4,'2012-01-27',16930.8)
INSERT INTO #mv VALUES (4,'2012-01-29',17237.65)
INSERT INTO #mv VALUES (4,'2012-01-31',17335.56)
INSERT INTO #mv VALUES (5,'2011-12-31',14610.35)
INSERT INTO #mv VALUES (5,'2012-01-04',15040.47)
INSERT INTO #mv VALUES (5,'2012-01-07',15349.56)
INSERT INTO #mv VALUES (5,'2012-01-11',16108.95)
INSERT INTO #mv VALUES (5,'2012-01-13',15648.46)
INSERT INTO #mv VALUES (5,'2012-01-16',16267.15)
INSERT INTO #mv VALUES (5,'2012-01-20',17035.03)
INSERT INTO #mv VALUES (5,'2012-01-23',16650.17)
INSERT INTO #mv VALUES (5,'2012-01-25',16256.93)
INSERT INTO #mv VALUES (5,'2012-01-26',16688.99)
INSERT INTO #mv VALUES (5,'2012-01-28',16639.72)
INSERT INTO #mv VALUES (5,'2012-01-31',16710.02)
INSERT INTO #mv VALUES (6,'2011-12-31',14587.05)
INSERT INTO #mv VALUES (6,'2012-01-05',15293.13)
INSERT INTO #mv VALUES (6,'2012-01-08',14966.98)
INSERT INTO #mv VALUES (6,'2012-01-14',15156.74)
INSERT INTO #mv VALUES (6,'2012-01-15',16182.28)
INSERT INTO #mv VALUES (6,'2012-01-16',16318.18)
INSERT INTO #mv VALUES (6,'2012-01-17',16741.96)
INSERT INTO #mv VALUES (6,'2012-01-19',17283.04)
INSERT INTO #mv VALUES (6,'2012-01-21',17362.69)
INSERT INTO #mv VALUES (6,'2012-01-23',17658.53)
INSERT INTO #mv VALUES (6,'2012-01-24',18187.72)
INSERT INTO #mv VALUES (6,'2012-01-26',18079.52)
INSERT INTO #mv VALUES (6,'2012-01-28',18270.48)
INSERT INTO #mv VALUES (6,'2012-01-31',18338.34)
INSERT INTO #mv VALUES (7,'2011-12-31',15493.07)
INSERT INTO #mv VALUES (7,'2012-01-03',15589.04)
INSERT INTO #mv VALUES (7,'2012-01-04',15766.93)
INSERT INTO #mv VALUES (7,'2012-01-05',16199.73)
INSERT INTO #mv VALUES (7,'2012-01-09',16765.12)
INSERT INTO #mv VALUES (7,'2012-01-11',17267.5)
INSERT INTO #mv VALUES (7,'2012-01-12',17421.47)
INSERT INTO #mv VALUES (7,'2012-01-14',17539.65)
INSERT INTO #mv VALUES (7,'2012-01-15',17556.64)
INSERT INTO #mv VALUES (7,'2012-01-16',17366.72)
INSERT INTO #mv VALUES (7,'2012-01-17',17036.57)
INSERT INTO #mv VALUES (7,'2012-01-20',17317.93)
INSERT INTO #mv VALUES (7,'2012-01-21',17229.89)
INSERT INTO #mv VALUES (7,'2012-01-22',16981.2)
INSERT INTO #mv VALUES (7,'2012-01-23',17440.47)
INSERT INTO #mv VALUES (7,'2012-01-25',17032.69)
INSERT INTO #mv VALUES (7,'2012-01-26',17284.51)
INSERT INTO #mv VALUES (7,'2012-01-27',17772.77)
INSERT INTO #mv VALUES (7,'2012-01-28',18166.43)
INSERT INTO #mv VALUES (7,'2012-01-31',18342.46)
INSERT INTO #mv VALUES (8,'2011-12-31',15722.74)
INSERT INTO #mv VALUES (8,'2012-01-03',16099.04)
INSERT INTO #mv VALUES (8,'2012-01-05',16082.3)
INSERT INTO #mv VALUES (8,'2012-01-06',16534.99)
INSERT INTO #mv VALUES (8,'2012-01-11',16725.38)
INSERT INTO #mv VALUES (8,'2012-01-12',16760.04)
INSERT INTO #mv VALUES (8,'2012-01-13',16432.87)
INSERT INTO #mv VALUES (8,'2012-01-14',16902.11)
INSERT INTO #mv VALUES (8,'2012-01-20',16763.25)
INSERT INTO #mv VALUES (8,'2012-01-21',17031.86)
INSERT INTO #mv VALUES (8,'2012-01-24',16955.53)
INSERT INTO #mv VALUES (8,'2012-01-26',16833.21)
INSERT INTO #mv VALUES (8,'2012-01-27',17307.71)
INSERT INTO #mv VALUES (8,'2012-01-29',17858.09)
INSERT INTO #mv VALUES (8,'2012-01-30',17618.78)
INSERT INTO #mv VALUES (8,'2012-01-31',17716.41)
INSERT INTO #mv VALUES (9,'2011-12-31',15314.82)
INSERT INTO #mv VALUES (9,'2012-01-03',15789.62)
INSERT INTO #mv VALUES (9,'2012-01-04',15874.77)
INSERT INTO #mv VALUES (9,'2012-01-05',16257.35)
INSERT INTO #mv VALUES (9,'2012-01-06',15910.62)
INSERT INTO #mv VALUES (9,'2012-01-08',16054.06)
INSERT INTO #mv VALUES (9,'2012-01-09',15967.82)
INSERT INTO #mv VALUES (9,'2012-01-14',16561.38)
INSERT INTO #mv VALUES (9,'2012-01-15',17051.1)
INSERT INTO #mv VALUES (9,'2012-01-16',16983.01)
INSERT INTO #mv VALUES (9,'2012-01-21',17603.76)
INSERT INTO #mv VALUES (9,'2012-01-24',17435.68)
INSERT INTO #mv VALUES (9,'2012-01-25',17159.46)
INSERT INTO #mv VALUES (9,'2012-01-28',17680.55)
INSERT INTO #mv VALUES (9,'2012-01-29',17279.74)
INSERT INTO #mv VALUES (9,'2012-01-30',17538.1)
INSERT INTO #mv VALUES (9,'2012-01-31',17662.49)
INSERT INTO #mv VALUES (10,'2011-12-31',15025.36)
INSERT INTO #mv VALUES (10,'2012-01-03',14570.57)
INSERT INTO #mv VALUES (10,'2012-01-06',14627.86)
INSERT INTO #mv VALUES (10,'2012-01-08',14826.02)
INSERT INTO #mv VALUES (10,'2012-01-10',15180.7)
INSERT INTO #mv VALUES (10,'2012-01-11',15353.16)
INSERT INTO #mv VALUES (10,'2012-01-13',15099.81)
INSERT INTO #mv VALUES (10,'2012-01-14',15636.38)
INSERT INTO #mv VALUES (10,'2012-01-17',15439.98)
INSERT INTO #mv VALUES (10,'2012-01-18',15586.85)
INSERT INTO #mv VALUES (10,'2012-01-20',15849.07)
INSERT INTO #mv VALUES (10,'2012-01-21',15548.2)
INSERT INTO #mv VALUES (10,'2012-01-22',15463.33)
INSERT INTO #mv VALUES (10,'2012-01-24',15696.9)
INSERT INTO #mv VALUES (10,'2012-01-26',15872.72)
INSERT INTO #mv VALUES (10,'2012-01-31',15884.25)
/*Calculate the time-weighted rate of return*/
SELECT Account
,wct.TWRR(c,d,mv) as TWRR
FROM (
      SELECT Account
      ,Trandate
      ,CASE PS   
            WHEN 'P' THEN TranAmt
            ELSE -TranAmt
       END
      ,'False'
      FROM #t4
      UNION ALL
      SELECT Account
      ,Trandate
      ,-emv
      ,'True'
      FROM #mv
      ) n(account, d, c, mv)
GROUP BY Account 
This produces the following result.
               Account                   TWRR
---------------------- ----------------------
                     1     0.0975186976118223
                     2     0.0770033966232755
                     3       0.08839331492087
                     4     0.0693993905057961
                     5     0.0664506003772058
                     6     0.0813486278503901
                     7     0.0866470429823187
                     8      0.056839427082984
                     9     0.0836794835923629
                    10     0.0631681752863544

 

See Also
·         MDIETZ - Modified Dietz

 



Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service