filter database

[pablomm]

I have a database with daily returns of a share for the last 5 years. But for my regression I want to use only the daily returns on the last day of each month. In other words, only 60 data that are the performance of the last day of each of the 60 months.

My problem is that I don't know how to filter the database so that it only shows the last days of each month.

library(quantmod)
MSFT<-getSymbols("MSFT", 
                 from="2015-07-01", src= "yahoo", 
                 auto.assign=F)[,4]

SP500<-getSymbols("^GSPC", 
                  from="2015-07-01", src= "yahoo", 
                  auto.assign=F)[,4]

MSFT<-dailyReturn(MSFT,type = 'arithmetic')
SP500<-dailyReturn(SP500, type = 'arithmetic')


SP500_BD<-as.data.frame(SP500$daily.returns)
MSFT_BD<-as.data.frame(MSFT$daily.returns)
SP500_BD$Time<-rownames(SP500_BD)
MSFT_BD$Time<-rownames(MSFT_BD)
DatosRS<-merge(SP500_BD,MSFT_BD,by="Time")


RLSDatosRS<-lm(formula = daily.returns.y  ~ daily.returns.x, DatosRS)
summary(RLSDatosRS)

[GreyMerchant]

Hello,

Welcome to the forum! You should just be able to simply filter the DatosRS dataset. It would help if I can see exactly what it is you are working with. Able to make a reprex? (See here: FAQ: How to do a minimal reproducible example ( reprex ) for beginners)

[andresrcs]

Is this what you mean?

library(quantmod)
library(lubridate)
library(dplyr)

MSFT<-getSymbols("MSFT", 
                 from="2015-07-01", src= "yahoo", 
                 auto.assign=F)[,4]

SP500<-getSymbols("^GSPC", 
                  from="2015-07-01", src= "yahoo", 
                  auto.assign=F)[,4]

MSFT<-dailyReturn(MSFT,type = 'arithmetic')
SP500<-dailyReturn(SP500, type = 'arithmetic')


SP500_BD<-as.data.frame(SP500$daily.returns)
MSFT_BD<-as.data.frame(MSFT$daily.returns)
SP500_BD$Time<-rownames(SP500_BD)
MSFT_BD$Time<-rownames(MSFT_BD)
DatosRS<-merge(SP500_BD,MSFT_BD,by="Time")

DatosRS %>% 
    group_by(year_month = paste(year(Time), month(Time), sep = "-")) %>% 
    filter(Time == max(Time))
#> # A tibble: 64 x 4
#> # Groups:   year_month [64]
#>    Time       daily.returns.x daily.returns.y year_month
#>    <chr>                <dbl>           <dbl> <chr>     
#>  1 2015-07-31        -0.00227       -0.00384  2015-7    
#>  2 2015-08-31        -0.00839       -0.00933  2015-8    
#>  3 2015-09-30         0.0191         0.0189   2015-9    
#>  4 2015-10-30        -0.00481       -0.0135   2015-10   
#>  5 2015-11-30        -0.00464        0.00779  2015-11   
#>  6 2015-12-31        -0.00941       -0.0147   2015-12   
#>  7 2016-01-29         0.0248         0.0582   2016-1    
#>  8 2016-02-29        -0.00812       -0.00819  2016-2    
#>  9 2016-03-31        -0.00204        0.00327  2016-3    
#> 10 2016-04-29        -0.00506       -0.000601 2016-4    
#> # … with 54 more rows

^{Created on 2020-10-02 by the reprex package (v0.3.0)}