Skip to content
Advertisement

Calculate returns and insert into another table

I have a table MF_NAVs which holds daily NAVs of mutual funds. I want to calculate the absolute returns over 1 day, 7 day, 15 days, 1 month, 3 month, 6 month, 1 year, 3 year, 5 year & since inception. And then INSERT the calculated values into another table MF_Returns.

The table structure and sample data for MF_NAVs is given below. I have also created a fiddle at the following link : https://www.db-fiddle.com/f/fYam96yYZo5tQLGLtcbhaQ/1

I use the SELECT query given below to calculate the absolute returns. The result from this query is also given below. My problem is I’m unable to save the result of this query into the MF_RETURNS table [structure given below].

The MF_NAVs table has around 20k different SchemeCode. Each SchemeCode has NAVs for different periods ranging from past few days to several months.

Please help save the calculated returns into MF_Returns table.

Query to calculate returns:

SELECT SchemeCode,Date,NetAssetValue, 
    ROUND((((L.LatestNAV)-NetAssetValue)/NetAssetValue)*100,2) AS AbsoluteReturns
FROM MF_NAVs 
JOIN (SELECT NetAssetValue AS LatestNAV FROM MF_NAVs WHERE SchemeCode=120503 ORDER BY DATE DESC LIMIT 1) AS L
WHERE SchemeCode=120503 
AND FIND_IN_SET(
      Date, 
      (
SELECT 
         CONCAT_WS( ',',
           Y.MaxDate, #LatestDate - MaxDate
           DATE_FORMAT(NOW() ,'%Y-01-01'), #YTD - Year To Datw
           MAX(CASE WHEN `Date` <= Y.MaxDate - INTERVAL 1 DAY THEN Date END), #1Day
           MAX(CASE WHEN `Date` <= Y.MaxDate - INTERVAL 7 DAY THEN Date END), #7Day
           MAX(CASE WHEN `Date` <= Y.MaxDate - INTERVAL 15 DAY THEN Date END), #15Day
           MAX(CASE WHEN `Date` <= Y.MaxDate - INTERVAL 1 MONTH THEN Date END), #1Month
           MAX(CASE WHEN `Date` <= Y.MaxDate - INTERVAL 3 MONTH THEN Date END), #3Month
           MAX(CASE WHEN `Date` <= Y.MaxDate - INTERVAL 6 MONTH THEN Date END), #6Month
           MAX(CASE WHEN `Date` <= Y.MaxDate - INTERVAL 1 YEAR THEN Date END), #1Year
           MAX(CASE WHEN `Date` <= Y.MaxDate - INTERVAL 3 YEAR THEN Date END), #3Year
           MAX(CASE WHEN `Date` <= Y.MaxDate - INTERVAL 5 YEAR THEN Date END), #5Year
           MIN(Date) #Inception
         )
       FROM MF_NAVs
       JOIN (SELECT MAX(Date) AS MaxDate FROM MF_NAVs WHERE SchemeCode=120503) AS Y
       WHERE SchemeCode=120503
          ) 
    )
ORDER BY Date DESC

Table Structure : MF_NAVs

CREATE TABLE `MF_NAVs` (
  `ID` int(11) NOT NULL,
  `SchemeCode` int(11) NOT NULL,
  `Date` date NOT NULL,
  `NetAssetValue` decimal(15,5) NOT NULL
)

Sample Data: MF_NAVs

+------------+------------+---------------+
| SchemeCode | Date       | NetAssetValue |
+------------+------------+---------------+
| 120503     | 2021-04-13 | 65.59480      |
+------------+------------+---------------+
| 120503     | 2021-04-12 | 65.22200      |
+------------+------------+---------------+
| 120503     | 2021-04-09 | 67.70330      |
+------------+------------+---------------+
| 120503     | 2021-04-08 | 67.89580      |
+------------+------------+---------------+
| 120503     | 2021-04-06 | 66.39850      |
+------------+------------+---------------+
| 120503     | 2021-04-05 | 66.11320      |
+------------+------------+---------------+
| 120503     | 2021-04-01 | 67.03710      |
+------------+------------+---------------+
| 120503     | 2021-03-26 | 65.79040      |
+------------+------------+---------------+
| 120503     | 2021-03-25 | 64.86400      |
+------------+------------+---------------+
| 120503     | 2021-03-12 | 68.38530      |
+------------+------------+---------------+
| 120503     | 2021-03-09 | 68.54100      |
+------------+------------+---------------+
| 120503     | 2021-01-13 | 66.05340      |
+------------+------------+---------------+
| 120503     | 2021-01-12 | 66.90930      |
+------------+------------+---------------+
| 120503     | 2021-01-08 | 66.77000      |
+------------+------------+---------------+
| 120503     | 2021-01-01 | 64.98200      |
+------------+------------+---------------+
| 120503     | 2020-10-13 | 51.78950      |
+------------+------------+---------------+
| 120503     | 2020-10-12 | 52.00310      |
+------------+------------+---------------+
| 120503     | 2020-10-09 | 52.06840      |
+------------+------------+---------------+
| 120503     | 2013-01-02 | 15.02530      |
+------------+------------+---------------+
| 120503     | 2016-04-08 | 29.94380      |
+------------+------------+---------------+
| 120503     | 2016-04-12 | 30.46710      |
+------------+------------+---------------+
| 120503     | 2016-04-13 | 30.94700      |
+------------+------------+---------------+
| 120503     | 2018-04-09 | 44.67440      |
+------------+------------+---------------+
| 120503     | 2018-04-12 | 45.10190      |
+------------+------------+---------------+
| 120503     | 2018-04-13 | 45.18520      |
+------------+------------+---------------+
| 120503     | 2020-04-09 | 43.82900      |
+------------+------------+---------------+
| 120503     | 2020-04-13 | 42.89580      |
+------------+------------+---------------+

Table Structure : MF_RETURNS

CREATE TABLE MF_RETURNS (
    `ID` int(11) NOT NULL,
    `SchemeCode` int(11) NOT NULL,
    `LatestDate` date NOT NULL,
    `YTDDate` date NOT NULL,
    `YTDAbsoluteReturns` decimal(15,5) NOT NULL,
    `1DayDate` date NOT NULL,
    `1DayAbsoluteReturns` decimal(15,5) NOT NULL, 
    `7DayDate` date NOT NULL,
    `7DayAbsoluteReturns` decimal(15,5) NOT NULL,
    `15DayDate` date NOT NULL,
    `15DayAbsoluteReturns` decimal(15,5) NOT NULL, 
    `1MonthDate` date NOT NULL,
    `1MonthAbsoluteReturns` decimal(15,5) NOT NULL, 
    `3MonthDate` date NOT NULL,
    `3MonthAbsoluteReturns` decimal(15,5) NOT NULL, 
    `6MonthDate` date NOT NULL,
    `6MonthAbsoluteReturns` decimal(15,5) NOT NULL, 
    `1YearDate` date NOT NULL,
    `1YearAbsoluteReturns` decimal(15,5) NOT NULL,
    `3YearDate` date NOT NULL,
    `3YearAbsoluteReturns` decimal(15,5) NOT NULL,
    `5YearDate` date NOT NULL,
    `5YearAbsoluteReturns` decimal(15,5) NOT NULL
    PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Query Output

+------------+------------+---------------+-----------------+
| SchemeCode | Date       | NetAssetValue | AbsoluteReturns |
+------------+------------+---------------+-----------------+
| 120503     | 2021-04-13 | 65.59480      | 0.00            |
+------------+------------+---------------+-----------------+
| 120503     | 2021-04-12 | 65.22200      | 0.57            |
+------------+------------+---------------+-----------------+
| 120503     | 2021-04-06 | 66.39850      | -1.21           |
+------------+------------+---------------+-----------------+
| 120503     | 2021-03-26 | 65.79040      | -0.30           |
+------------+------------+---------------+-----------------+
| 120503     | 2021-03-12 | 68.38530      | -4.08           |
+------------+------------+---------------+-----------------+
| 120503     | 2021-01-13 | 66.05340      | -0.69           |
+------------+------------+---------------+-----------------+
| 120503     | 2021-01-01 | 64.98200      | 0.94            |
+------------+------------+---------------+-----------------+
| 120503     | 2020-10-13 | 51.78950      | 26.66           |
+------------+------------+---------------+-----------------+
| 120503     | 2020-04-13 | 42.89580      | 52.92           |
+------------+------------+---------------+-----------------+
| 120503     | 2018-04-13 | 45.18520      | 45.17           |
+------------+------------+---------------+-----------------+
| 120503     | 2016-04-13 | 30.94700      | 111.96          |
+------------+------------+---------------+-----------------+
| 120503     | 2013-01-02 | 15.02530      | 336.56          |
+------------+------------+---------------+-----------------+

Advertisement

Answer

ONLY TEMP SAMPLE. I will correct it after some questions are cleared.

SELECT
    null as ID,
    SchemeCode
    
    , MAX( IF(id=3, r.`Date`,null) ) AS 1DayDate
    , MAX( IF(id=3, r.`AbsoluteReturns`,null) ) AS 1DayAbsoluteReturns
    
    , MAX( IF(id=4, r.`Date`,null) ) AS 7DayDate
    , MAX( IF(id=4, r.`AbsoluteReturns`,null) ) AS 7DayAbsoluteReturns
    
    , MAX( IF(id=5, r.`Date`,null) ) AS 15DayDate
    , MAX( IF(id=5, r.`AbsoluteReturns`,null) ) AS 15DayAbsoluteReturns
    
    , MAX( IF(id=6, r.`Date`,null) ) AS 1MonthDate
    , MAX( IF(id=6, r.`AbsoluteReturns`,null) ) AS 1MonthAbsoluteReturns
    
    , MAX( IF(id=7, r.`Date`,null) ) AS 3MonthDate
    , MAX( IF(id=7, r.`AbsoluteReturns`,null) ) AS 3MonthAbsoluteReturns
    
    , MAX( IF(id=9, r.`Date`,null) ) AS 6MonthDate
    , MAX( IF(id=8, r.`AbsoluteReturns`,null) ) AS 6MonthAbsoluteReturns
    
    , MAX( IF(id=10, r.`Date`,null) ) AS 1YearDate
    , MAX( IF(id=10, r.`AbsoluteReturns`,null) ) AS 1YearAbsoluteReturns
    
    , MAX( IF(id=11, r.`Date`,null) ) AS 3YearDate
    , MAX( IF(id=11, r.`AbsoluteReturns`,null) ) AS 3YearAbsoluteReturns
    
    , MAX( IF(id=12, r.`Date`,null) ) AS 5YearDate
    , MAX( IF(id=12, r.`AbsoluteReturns`,null) ) AS 5YearAbsoluteReturns
    
FROM (

SELECT
    ROW_NUMBER() OVER (PARTITION BY `SchemeCode` order by `Date` desc ) AS id,  
    SchemeCode,Date,NetAssetValue,
    ROUND((((L.LatestNAV)-NetAssetValue)/NetAssetValue)*100,2) AS AbsoluteReturns
FROM MF_NAVs 
JOIN (SELECT NetAssetValue AS LatestNAV FROM MF_NAVs WHERE SchemeCode=120503 ORDER BY DATE DESC LIMIT 1) AS L
WHERE SchemeCode=120503 
AND FIND_IN_SET(
      Date, 
      (
SELECT 
         CONCAT_WS( ',',
           Y.MaxDate, #LatestDate - MaxDate
           DATE_FORMAT(NOW() ,'%Y-01-01'), #YTD - Year To Datw
           MAX(CASE WHEN `Date` <= Y.MaxDate - INTERVAL 1 DAY THEN Date END), #1Day
           MAX(CASE WHEN `Date` <= Y.MaxDate - INTERVAL 7 DAY THEN Date END), #7Day
           MAX(CASE WHEN `Date` <= Y.MaxDate - INTERVAL 15 DAY THEN Date END), #15Day
           MAX(CASE WHEN `Date` <= Y.MaxDate - INTERVAL 1 MONTH THEN Date END), #1Month
           MAX(CASE WHEN `Date` <= Y.MaxDate - INTERVAL 3 MONTH THEN Date END), #3Month
           MAX(CASE WHEN `Date` <= Y.MaxDate - INTERVAL 6 MONTH THEN Date END), #6Month
           MAX(CASE WHEN `Date` <= Y.MaxDate - INTERVAL 1 YEAR THEN Date END), #1Year
           MAX(CASE WHEN `Date` <= Y.MaxDate - INTERVAL 3 YEAR THEN Date END), #3Year
           MAX(CASE WHEN `Date` <= Y.MaxDate - INTERVAL 5 YEAR THEN Date END), #5Year
           MIN(Date) #Inception
         )
       FROM MF_NAVs
       JOIN (SELECT MAX(Date) AS MaxDate FROM MF_NAVs WHERE SchemeCode=120503) AS Y
       WHERE SchemeCode=120503
          ) 
    )
ORDER BY Date DESC 

) r
GROUP BY r.SchemeCode;
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement