Simple Returns and Monthly Returns from daily stock price observations with Missing data in R
<p>I have a data frame which has over 4000 columns and 3000 rows. Columns are companies and rows have daily stock closing price. The data is such that it has missing values but due to nature of the data those can't be removed.
Now, I want to calculate simple returns and monthly returns from these daily stock closing price observations.I have daily observation for 15 years.
As, Simple return = (Today's price/Yesterday's price)-1
Monthly return = (Price for last business day of month/Price for last business day of previousmonth)-1</p>

<p>I illustrate my dataframe, first two months out of 15 years data</p>

<pre><code>Date         A       B      C   D
30/12/1999  79.5    325     NA  961
04/01/2000  79.5    325     NA  961
05/01/2000  79.5    322.5   NA  945
06/01/2000  79.5    327.5   NA  952
07/01/2000  79.5    327.5   NA  941
10/01/2000  79.5    327.5   NA  946
11/01/2000  79.5    327.5   NA  888
12/01/2000  79.5    331.5   NA  870
13/01/2000  79.5    334     NA  853
14/01/2000  79.5    334     NA  834
17/01/2000  94.5    350     NA  799
18/01/2000  95.5    351.5   NA  770
19/01/2000  94.5    352.5   NA  763
20/01/2000  97.5    352.5   NA  730
21/01/2000  97.5    352.5   NA  765
24/01/2000  97.5    352.5   NA  743
25/01/2000  97.5    352.5   NA  737
26/01/2000  97.5    352.5   NA  777
27/01/2000  97.5    351.5   NA  800
28/01/2000  96.5    350     NA  770
31/01/2000  96.5    350     NA  747
01/02/2000  101.5   350     NA  719
02/02/2000  118.5   350     NA  723
03/02/2000  117     350     NA  732
04/02/2000  117     350     NA  673
07/02/2000  117     317.5   NA  671
08/02/2000  117     313.5   NA  665
09/02/2000  117     312.5   NA  655
10/02/2000  114.5   318.5   NA  684
11/02/2000  114.5   326.5   NA  655
14/02/2000  111     337.5   NA  634
15/02/2000  111     337     NA  647
16/02/2000  111     337     NA  666
17/02/2000  111     334.5   NA  713
18/02/2000  111     334.5   NA  726
21/02/2000  112     332.5   NA  714
22/02/2000  111     331.5   NA  653
23/02/2000  119     332.5   NA  658
24/02/2000  125     332.5   NA  646
25/02/2000  125     350.5   NA  633
28/02/2000  125     350.5   NA  645
29/02/2000  125     350.5   NA  711
</code></pre>

<p>Desired simple returns, to save space I have just presented some part of desired out</p>

<pre><code>Date        A    B      C    D
30/12/1999              
04/01/2000  0    0      NA   0
05/01/2000  0   -0.01   NA  -0.02
06/01/2000  0    0.02   NA   0.01
07/01/2000  0    0.00   NA  -0.01
10/01/2000  0    0.00   NA   0.01
11/01/2000  0    0.00   NA  -0.06
12/01/2000  0    0.01   NA  -0.02
13/01/2000  0    0.01   NA  -0.02
14/01/2000  0    0.00   NA  -0.02
17/01/2000  0.19 0.05   NA  -0.04
18/01/2000  0.01 0.00   NA  -0.04
19/01/2000 -0.01 0.00   NA  -0.01
20/01/2000  0.03    0   NA  -0.04
</code></pre>

<p>Desired output for monthly data</p>

<pre><code>Month     A      B      C    D
Jan-00  0.21    0.076   NA  -0.22
Feb-00  0.29    0.001   NA  -0.04
</code></pre>