1

I am doing data analysis in R to get the pre-ranking betas of Fama-French CAPM model. In order to calculate the pre-ranking betas, I have to regress a minimum of 24 and a maximum of 60 monthly returns (R) on market returns (M) and save the betas (first coefficient of the regression analysis) for EACH month (I have 342 months) after the 60th month. In order to achieve this, I created the following loop: 

mydata <- read.csv("Austria.csv", header = T, sep = ";")

Betas <- matrix(nrow = 342, ncol = 1)

for(k in 1:282){
  M <- mydata[k:59+k,3]
  R <- mydata[k:59+k,4] - mydata[k:59+k,2]
  criteria <- length(!is.na(R))
  if (criteria >= 24) {
    result <- lm(R ~ M, na.action = na.omit)
  } else {
    result <- "NA"
  } 
  if (any(result != "NA")) {
    coefficients <- coef(summary(result))
  } else {
    coefficients <- "NA"
  } 
  if (any(coefficients != "NA")) {
    Betas[k,1] <- coefficients[2,1]
  } else {
    Betas[k,1] <- "NA"
  }
}

But the betas generated through this procedure do not match the regression betas in excel. Can anyone please help me?

The data that I'm using consists of 342 rows and 4 columns. A subset of my data is here: 

structure(list(Date = structure(c(188L, 217L, 246L, 275L, 304L, 
333L, 20L, 48L, 76L, 104L, 132L, 160L, 189L, 218L, 247L, 276L, 
305L, 334L, 21L, 49L, 77L, 105L, 133L, 161L, 190L, 219L, 248L, 
277L, 306L, 335L, 22L, 50L, 78L, 106L, 134L, 162L, 191L, 220L, 
249L, 278L, 307L, 336L, 23L, 51L, 79L, 107L, 135L, 163L, 192L, 
221L, 250L, 279L, 308L, 337L, 24L, 52L, 80L, 108L, 136L, 164L, 
193L, 222L, 251L, 280L, 309L, 338L, 25L, 53L, 81L, 109L, 137L, 
165L, 194L, 223L, 252L, 281L, 310L, 339L, 26L, 54L, 82L, 110L, 
138L, 166L, 195L, 224L, 253L, 282L, 311L, 340L, 27L, 55L, 83L, 
111L, 139L, 167L, 196L, 225L, 254L, 283L), .Label = c("21.01.00", 
"21.01.01", "21.01.02", "21.01.03", "21.01.04", "21.01.05", "21.01.06", 
"21.01.07", "21.01.08", "21.01.09", "21.01.10", "21.01.11", "21.01.12", 
"21.01.13", "21.01.14", "21.01.15", "21.01.16", "21.01.17", "21.01.18", 
"21.01.91", "21.01.92", "21.01.93", "21.01.94", "21.01.95", "21.01.96", 
"21.01.97", "21.01.98", "21.01.99", "21.02.00", "21.02.01", "21.02.02", 
"21.02.03", "21.02.04", "21.02.05", "21.02.06", "21.02.07", "21.02.08", 
"21.02.09", "21.02.10", "21.02.11", "21.02.12", "21.02.13", "21.02.14", 
"21.02.15", "21.02.16", "21.02.17", "21.02.18", "21.02.91", "21.02.92", 
"21.02.93", "21.02.94", "21.02.95", "21.02.96", "21.02.97", "21.02.98", 
"21.02.99", "21.03.00", "21.03.01", "21.03.02", "21.03.03", "21.03.04", 
"21.03.05", "21.03.06", "21.03.07", "21.03.08", "21.03.09", "21.03.10", 
"21.03.11", "21.03.12", "21.03.13", "21.03.14", "21.03.15", "21.03.16", 
"21.03.17", "21.03.18", "21.03.91", "21.03.92", "21.03.93", "21.03.94", 
"21.03.95", "21.03.96", "21.03.97", "21.03.98", "21.03.99", "21.04.00", 
"21.04.01", "21.04.02", "21.04.03", "21.04.04", "21.04.05", "21.04.06", 
"21.04.07", "21.04.08", "21.04.09", "21.04.10", "21.04.11", "21.04.12", 
"21.04.13", "21.04.14", "21.04.15", "21.04.16", "21.04.17", "21.04.18", 
"21.04.91", "21.04.92", "21.04.93", "21.04.94", "21.04.95", "21.04.96", 
"21.04.97", "21.04.98", "21.04.99", "21.05.00", "21.05.01", "21.05.02", 
"21.05.03", "21.05.04", "21.05.05", "21.05.06", "21.05.07", "21.05.08", 
"21.05.09", "21.05.10", "21.05.11", "21.05.12", "21.05.13", "21.05.14", 
"21.05.15", "21.05.16", "21.05.17", "21.05.18", "21.05.91", "21.05.92", 
"21.05.93", "21.05.94", "21.05.95", "21.05.96", "21.05.97", "21.05.98", 
"21.05.99", "21.06.00", "21.06.01", "21.06.02", "21.06.03", "21.06.04", 
"21.06.05", "21.06.06", "21.06.07", "21.06.08", "21.06.09", "21.06.10", 
"21.06.11", "21.06.12", "21.06.13", "21.06.14", "21.06.15", "21.06.16", 
"21.06.17", "21.06.18", "21.06.91", "21.06.92", "21.06.93", "21.06.94", 
"21.06.95", "21.06.96", "21.06.97", "21.06.98", "21.06.99", "21.07.00", 
"21.07.01", "21.07.02", "21.07.03", "21.07.04", "21.07.05", "21.07.06", 
"21.07.07", "21.07.08", "21.07.09", "21.07.10", "21.07.11", "21.07.12", 
"21.07.13", "21.07.14", "21.07.15", "21.07.16", "21.07.17", "21.07.18", 
"21.07.90", "21.07.91", "21.07.92", "21.07.93", "21.07.94", "21.07.95", 
"21.07.96", "21.07.97", "21.07.98", "21.07.99", "21.08.00", "21.08.01", 
"21.08.02", "21.08.03", "21.08.04", "21.08.05", "21.08.06", "21.08.07", 
"21.08.08", "21.08.09", "21.08.10", "21.08.11", "21.08.12", "21.08.13", 
"21.08.14", "21.08.15", "21.08.16", "21.08.17", "21.08.18", "21.08.90", 
"21.08.91", "21.08.92", "21.08.93", "21.08.94", "21.08.95", "21.08.96", 
"21.08.97", "21.08.98", "21.08.99", "21.09.00", "21.09.01", "21.09.02", 
"21.09.03", "21.09.04", "21.09.05", "21.09.06", "21.09.07", "21.09.08", 
"21.09.09", "21.09.10", "21.09.11", "21.09.12", "21.09.13", "21.09.14", 
"21.09.15", "21.09.16", "21.09.17", "21.09.18", "21.09.90", "21.09.91", 
"21.09.92", "21.09.93", "21.09.94", "21.09.95", "21.09.96", "21.09.97", 
"21.09.98", "21.09.99", "21.10.00", "21.10.01", "21.10.02", "21.10.03", 
"21.10.04", "21.10.05", "21.10.06", "21.10.07", "21.10.08", "21.10.09", 
"21.10.10", "21.10.11", "21.10.12", "21.10.13", "21.10.14", "21.10.15", 
"21.10.16", "21.10.17", "21.10.18", "21.10.90", "21.10.91", "21.10.92", 
"21.10.93", "21.10.94", "21.10.95", "21.10.96", "21.10.97", "21.10.98", 
"21.10.99", "21.11.00", "21.11.01", "21.11.02", "21.11.03", "21.11.04", 
"21.11.05", "21.11.06", "21.11.07", "21.11.08", "21.11.09", "21.11.10", 
"21.11.11", "21.11.12", "21.11.13", "21.11.14", "21.11.15", "21.11.16", 
"21.11.17", "21.11.18", "21.11.90", "21.11.91", "21.11.92", "21.11.93", 
"21.11.94", "21.11.95", "21.11.96", "21.11.97", "21.11.98", "21.11.99", 
"21.12.00", "21.12.01", "21.12.02", "21.12.03", "21.12.04", "21.12.05", 
"21.12.06", "21.12.07", "21.12.08", "21.12.09", "21.12.10", "21.12.11", 
"21.12.12", "21.12.13", "21.12.14", "21.12.15", "21.12.16", "21.12.17", 
"21.12.18", "21.12.90", "21.12.91", "21.12.92", "21.12.93", "21.12.94", 
"21.12.95", "21.12.96", "21.12.97", "21.12.98", "21.12.99"), class = "factor"), 
    RF = c(0.0068, 0.0066, 0.006, 0.0068, 0.0057, 0.006, 0.0052, 
    0.0048, 0.0044, 0.0053, 0.0047, 0.0042, 0.0049, 0.0046, 0.0046, 
    0.0042, 0.0039, 0.0038, 0.0034, 0.0028, 0.0034, 0.0032, 0.0028, 
    0.0032, 0.0031, 0.0026, 0.0026, 0.0023, 0.0023, 0.0028, 0.0023, 
    0.0022, 0.0025, 0.0024, 0.0022, 0.0025, 0.0024, 0.0025, 0.0026, 
    0.0022, 0.0025, 0.0023, 0.0025, 0.0021, 0.0027, 0.0027, 0.0032, 
    0.0031, 0.0028, 0.0037, 0.0037, 0.0038, 0.0037, 0.0044, 0.0042, 
    0.004, 0.0046, 0.0045, 0.0054, 0.0047, 0.0045, 0.0047, 0.0043, 
    0.0047, 0.0042, 0.0049, 0.0043, 0.0039, 0.0039, 0.0046, 0.0042, 
    0.004, 0.0045, 0.0041, 0.0044, 0.0042, 0.0041, 0.0046, 0.0045, 
    0.0039, 0.0043, 0.0043, 0.0049, 0.0037, 0.0043, 0.0041, 0.0044, 
    0.0042, 0.0039, 0.0048, 0.0043, 0.0039, 0.0039, 0.0043, 0.004, 
    0.0041, 0.004, 0.0043, 0.0046, 0.0032), Mkt.RF = c(0.0446, 
    -0.1088, -0.1219, 0.0645, -0.0042, -0.0155, 0.0151, 0.073, 
    -0.067, -0.0087, 0.0049, -0.0741, 0.053, 0.0142, 0.0302, 
    -0.0262, -0.0227, 0.063, -0.0052, -0.0044, -0.0373, 0.0502, 
    0.0509, -0.0117, -0.041, -0.008, -0.0299, -0.0771, -0.0065, 
    0.0122, 0.0063, 0.0111, 0.0543, 0.0192, 0.0091, -0.0323, 
    0.0103, 0.0861, -0.0064, 0.0246, -0.0231, 0.0716, 0.0589, 
    -0.0346, -0.028, 0.0379, -0.04, -0.0116, 0.0466, 0.019, -0.0358, 
    0.0359, -0.0454, 2e-04, -0.0155, 0.0197, 0.0345, 0.0256, 
    0.0176, 0.01, 0.0399, -0.0405, 0.0237, -0.0071, -0.0032, 
    0.0184, 0.0044, 0.0188, 0.0089, 0.0062, 0.015, 0.0022, -0.0179, 
    0.0226, 0.0118, 0.0155, 0.0408, 0.0201, -0.0035, 0.0068, 
    0.0204, -0.0075, 0.0414, 0.0366, 0.0312, -0.0529, 0.0874, 
    -0.0488, 0.0075, 0.0226, 0.0316, 0.0749, 0.0691, 0.0219, 
    0.0222, -0.005, 0.0152, -0.1208, -0.0484, 0.0659), OBERBANK.AG = c(0.099118943, 
    -0.190380762, -0.108415842, 0.023875625, -0.009219089, -0.075533662, 
    -0.039668443, 0.103575832, -0.111731844, -0.018867925, -0.028205128, 
    -0.051451187, -0.004172462, -0.073324022, 0.091936699, -0.031746032, 
    0.025659301, 0.045170257, -0.021276596, -0.00611413, -0.040328093, 
    -0.013532764, 0.041877256, 0.015246015, 0.027986348, -0.011288181, 
    -0.018132975, 0.012995896, -0.049966239, 0.026297086, -0.027700831, 
    0.029202279, -0.04083045, 0.011544012, -0.012838802, -0.031791908, 
    0.003731343, 0.261710037, 0.076605775, -0.032840722, 0.019241653, 
    0.034980566, 0.061158798, -0.050050556, -0.032464077, -0.031353135, 
    0.010789324, 0.012359551, 0.029411765, -0.026415094, -0.014396456, 
    0.019101124, -0.047960309, -0.011580776, 0.041593439, -0.007311586, 
    0.061189802, 0.050720769, -0.045223577, 0.024481107, -0.004675325, 
    -0.04697286, 0.004928806, 0.027792916, 0, -0.020678685, -0.012452626, 
    0, -0.014802632, -0.001669449, -0.011705686, -0.009024253, 
    0.027319294, 0.004432133, -0.023166023, -0.006775833, 0.05173394, 
    -0.035135135, -0.038655462, -0.016317016, 0.071682464, -0.011055832, 
    -0.008943544, -0.0107163, -0.022805017, 0.057176196, 0.025386313, 
    -0.026910657, 0.005530973, 0, -0.04180418, 0.061997704, 0.274594595, 
    -0.046649703, 0.018683274, -0.064628821, -0.038281979, -0.026213592, 
    0.0777667, 0.051341351)), row.names = c(NA, 100L), class = "data.frame")
acylam
  • 18,231
  • 5
  • 36
  • 45
Moheb Faghiri
  • 11
  • 3
  • Can you provide a reproducible example? It would be easier to help if you can provide a sample data and an expected output. https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example – DS_UNI Feb 22 '19 at 13:52
  • Welcome to Stack Overflow! Can you please post a sample of your csv file? People need to test your code to find the error. If your file is large (50+ rows), take a subset of rows. Use `dput()` to convert the data to text. [Edit] your question to include the data. Also, it might help if you post your Excel formula. – akraf Feb 22 '19 at 13:55
  • @akraf thank you for your assistance. I just put a subset of my data. I am very new to R and Stack Overflow and I don't know the procedures, please pardon me. Thanks! – Moheb Faghiri Feb 22 '19 at 15:42
  • @DS_UNI thank you for your assistance. I just put a subset of my data. I am very new to R and Stack Overflow and I don't know the procedures, please pardon me. Thanks! – Moheb Faghiri Feb 22 '19 at 15:42
  • @DS_UNI in excel I simply regress first 60 observations of the 4th - 2nd column on the 3rd column (if you have a look at the data I just added to the question, you will understand what I want to convey). The beta that I get from excel is 0.90531 which is different from the one that I get through this R code. I need to do the same procedure to all the rows in my data. – Moheb Faghiri Feb 22 '19 at 15:46
  • you have a bug in the code which is causing your miscalculations. You need to add brackets in `k:59+k` meaning: `k:(59+k)` because the first one is taking the vector from `k` to 59 and then adding the `k` to it, so for `k = 1 `, for example, you're sub-setting the rows `2:60` (from 2 to 60 ) – DS_UNI Feb 22 '19 at 17:29
  • @DS_UNI yeah, it worked. Thank you. You saved me :) – Moheb Faghiri Feb 22 '19 at 19:54

0 Answers0