Destring Factor Data

[Robert Prins]

Dear all,

For my thesis, I have downloaded factor data from Kenneth French's website in a csv format. When importing into stata the data looks as follows:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str58 v1
"This file was created using the 202103 Bloomberg database."
"Missing data are indicated by -99.99."                     
",Mkt-RF,SMB,HML,RF"                                        
"19900702    ,0.99    ,0.05   ,-0.53    ,0.03"              
"19900703    ,0.33   ,-0.12   ,-0.03    ,0.03"              
"19900704    ,0.24   ,-0.01   ,-0.14    ,0.03"              
"19900705   ,-0.64    ,0.19    ,0.06    ,0.03"              
"19900706    ,0.08   ,-0.19    ,0.14    ,0.03"              
"19900709    ,0.49    ,0.03   ,-0.27    ,0.03"              
"19900710    ,0.01    ,0.36   ,-0.39    ,0.03"              
"19900711   ,-0.34   ,-0.49   ,-0.05    ,0.03"              
"19900712    ,0.61   ,-0.16   ,-0.02    ,0.03"              
"19900713    ,0.48   ,-0.61    ,0.03    ,0.03"              
"19900716    ,0.31   ,-0.21   ,-0.22    ,0.03"              
"19900717    ,0.55    ,0.15    ,0.26    ,0.03"              
"19900718    ,0.12   ,-0.08   ,-0.03    ,0.03"              
"19900719   ,-0.13    ,0.30   ,-0.19    ,0.03"              
"19900720    ,0.34   ,-0.10    ,0.11    ,0.03"              
"19900723   ,-0.07    ,0.66   ,-0.01    ,0.03"              
"19900724   ,-0.38    ,0.00    ,0.17    ,0.03"              
"19900725    ,0.15   ,-0.19    ,0.28    ,0.03"              
"19900726   ,-0.30    ,0.35   ,-0.13    ,0.03"              
"19900727    ,0.21    ,0.49   ,-0.24    ,0.03"              
"19900730   ,-0.04    ,0.15    ,0.06    ,0.03"              
"19900731    ,1.38   ,-0.36   ,-0.11    ,0.03"              
"19900801   ,-0.40   ,-0.01    ,0.08    ,0.03"              
"19900802   ,-1.38    ,0.37    ,0.11    ,0.03"              
"19900803   ,-1.23    ,0.70   ,-0.04    ,0.03"              
"19900806   ,-2.85    ,1.38   ,-0.47    ,0.03"              
"19900807    ,0.51   ,-1.22    ,0.28    ,0.03"              
"19900808   ,-1.63    ,0.63   ,-0.03    ,0.03"              
"19900809    ,0.29   ,-0.75   ,-0.24    ,0.03"              
"19900810   ,-0.06    ,0.05    ,0.14    ,0.03"              
"19900813   ,-0.84    ,0.49   ,-0.07    ,0.03"              
"19900814    ,0.57   ,-0.39    ,0.13    ,0.03"              
"19900815    ,0.84   ,-0.43   ,-0.03    ,0.03"              
"19900816   ,-0.17    ,0.20    ,0.23    ,0.03"              
"19900817   ,-1.57    ,0.94    ,0.15    ,0.03"              
"19900820   ,-2.17    ,0.39    ,0.31    ,0.03"              
"19900821   ,-2.56    ,0.88    ,0.59    ,0.03"              
"19900822    ,0.25   ,-0.85    ,0.26    ,0.03"              
"19900823   ,-1.39    ,0.48   ,-0.41    ,0.03"              
"19900824    ,0.35   ,-1.21    ,0.25    ,0.03"              
"19900827    ,2.76   ,-1.58    ,0.43    ,0.03"              
"19900828    ,0.61    ,0.47   ,-0.93    ,0.03"              
"19900829    ,0.09   ,-0.40   ,-0.48    ,0.03"              
"19900830   ,-0.09   ,-0.42   ,-0.25    ,0.03"              
"19900831   ,-1.15    ,0.32   ,-0.23    ,0.03"              
"19900903   ,-1.38    ,0.15    ,0.15    ,0.03"              
"19900904   ,-1.03    ,0.54    ,0.12    ,0.03"              
"19900905    ,1.12   ,-0.20   ,-0.06    ,0.03"              
"19900906   ,-0.61    ,0.41    ,0.01    ,0.03"              
"19900907   ,-0.79   ,-0.21   ,-0.04    ,0.03"              
"19900910    ,0.07   ,-0.94   ,-0.06    ,0.03"              
"19900911   ,-0.93    ,0.41   ,-0.02    ,0.03"              
"19900912    ,0.39   ,-0.19    ,0.26    ,0.03"              
"19900913    ,0.45    ,0.35    ,0.00    ,0.03"              
"19900914   ,-0.52    ,0.72   ,-0.03    ,0.03"              
"19900917   ,-0.20    ,0.09   ,-0.04    ,0.03"              
"19900918   ,-0.99    ,0.26    ,0.18    ,0.03"              
"19900919   ,-1.97   ,-0.07   ,-0.01    ,0.03"              
"19900920   ,-2.38    ,0.49   ,-0.41    ,0.03"              
"19900921   ,-1.19   ,-0.82    ,0.23    ,0.03"              
"19900924   ,-0.26    ,0.72    ,0.39    ,0.03"              
"19900925   ,-1.64    ,0.32   ,-0.10    ,0.03"              
"19900926   ,-0.19   ,-0.28   ,-0.09    ,0.03"              
"19900927    ,0.33   ,-0.17    ,0.24    ,0.03"              
"19900928   ,-1.08    ,0.66   ,-0.10    ,0.03"              
"19901001    ,2.66   ,-2.07   ,-0.22    ,0.03"              
"19901002    ,1.83   ,-0.95   ,-0.25    ,0.03"              
"19901003    ,0.88   ,-0.06    ,0.00    ,0.03"              
"19901004    ,0.00    ,0.51    ,0.03    ,0.03"              
"19901005    ,0.94   ,-0.55   ,-0.24    ,0.03"              
"19901008    ,2.97   ,-0.06    ,0.20    ,0.03"              
"19901009   ,-1.11    ,0.65   ,-0.04    ,0.03"              
"19901010   ,-1.26    ,0.72   ,-0.02    ,0.03"              
"19901011    ,0.22   ,-0.44   ,-0.47    ,0.03"              
"19901012    ,0.46   ,-0.26    ,0.35    ,0.03"              
"19901015   ,-0.25   ,-0.28   ,-0.17    ,0.03"              
"19901016    ,0.46    ,0.45    ,0.03    ,0.03"              
"19901017   ,-0.54    ,0.22    ,0.10    ,0.03"              
"19901018    ,1.08   ,-0.50   ,-0.10    ,0.03"              
"19901019    ,0.39   ,-0.78   ,-0.05    ,0.03"              
"19901022   ,-0.34   ,-0.18   ,-0.02    ,0.03"              
"19901023    ,1.21   ,-0.91    ,0.12    ,0.03"              
"19901024    ,0.34    ,0.47    ,0.21    ,0.03"              
"19901025   ,-0.66    ,0.55   ,-0.07    ,0.03"              
"19901026   ,-1.45    ,0.74   ,-0.34    ,0.03"              
"19901029   ,-0.61   ,-0.03    ,0.10    ,0.03"              
"19901030   ,-0.86    ,0.62   ,-0.16    ,0.03"              
"19901031    ,0.02   ,-0.51    ,0.11    ,0.03"              
"19901101   ,-0.27    ,0.35    ,0.07    ,0.03"              
"19901102    ,0.23   ,-0.12    ,0.02    ,0.03"              
"19901105    ,0.98   ,-0.32   ,-0.20    ,0.03"              
"19901106    ,0.06   ,-0.23    ,0.19    ,0.03"              
"19901107   ,-0.62    ,0.51    ,0.11    ,0.03"              
"19901108   ,-1.08    ,0.31   ,-0.03    ,0.03"              
"19901109    ,0.17   ,-0.42    ,0.12    ,0.03"              
"19901112    ,0.68   ,-0.80   ,-0.06    ,0.03"              
"19901113    ,0.33   ,-0.32    ,0.05    ,0.03"              
end

I am new to stata and do not know how to destring above data. I wish to keep the date, Mkt-rf, SMB, HML and RF all as seperate variables. Any help would be much appreciated!
Thank you in advance.

Kind regards,

Robert

[Nick Cox]

In addition to the code here, you need a business calendar, as weekdays only are shown.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str58 v1
"This file was created using the 202103 Bloomberg database."
"Missing data are indicated by -99.99."                     
",Mkt-RF,SMB,HML,RF"                                        
"19900702    ,0.99    ,0.05   ,-0.53    ,0.03"              
"19900703    ,0.33   ,-0.12   ,-0.03    ,0.03"              
"19900704    ,0.24   ,-0.01   ,-0.14    ,0.03"              
"19900705   ,-0.64    ,0.19    ,0.06    ,0.03"              
"19900706    ,0.08   ,-0.19    ,0.14    ,0.03"              
"19900709    ,0.49    ,0.03   ,-0.27    ,0.03"              
"19900710    ,0.01    ,0.36   ,-0.39    ,0.03"              
"19900711   ,-0.34   ,-0.49   ,-0.05    ,0.03"              
"19900712    ,0.61   ,-0.16   ,-0.02    ,0.03"              
"19900713    ,0.48   ,-0.61    ,0.03    ,0.03"              
"19900716    ,0.31   ,-0.21   ,-0.22    ,0.03"              
"19900717    ,0.55    ,0.15    ,0.26    ,0.03"              
"19900718    ,0.12   ,-0.08   ,-0.03    ,0.03"              
"19900719   ,-0.13    ,0.30   ,-0.19    ,0.03"              
"19900720    ,0.34   ,-0.10    ,0.11    ,0.03"              
"19900723   ,-0.07    ,0.66   ,-0.01    ,0.03"              
"19900724   ,-0.38    ,0.00    ,0.17    ,0.03"              
"19900725    ,0.15   ,-0.19    ,0.28    ,0.03"              
"19900726   ,-0.30    ,0.35   ,-0.13    ,0.03"              
"19900727    ,0.21    ,0.49   ,-0.24    ,0.03"              
"19900730   ,-0.04    ,0.15    ,0.06    ,0.03"              
"19900731    ,1.38   ,-0.36   ,-0.11    ,0.03"              
"19900801   ,-0.40   ,-0.01    ,0.08    ,0.03"              
"19900802   ,-1.38    ,0.37    ,0.11    ,0.03"              
"19900803   ,-1.23    ,0.70   ,-0.04    ,0.03"              
"19900806   ,-2.85    ,1.38   ,-0.47    ,0.03"              
"19900807    ,0.51   ,-1.22    ,0.28    ,0.03"              
"19900808   ,-1.63    ,0.63   ,-0.03    ,0.03"              
"19900809    ,0.29   ,-0.75   ,-0.24    ,0.03"              
"19900810   ,-0.06    ,0.05    ,0.14    ,0.03"              
"19900813   ,-0.84    ,0.49   ,-0.07    ,0.03"              
"19900814    ,0.57   ,-0.39    ,0.13    ,0.03"              
"19900815    ,0.84   ,-0.43   ,-0.03    ,0.03"              
"19900816   ,-0.17    ,0.20    ,0.23    ,0.03"              
"19900817   ,-1.57    ,0.94    ,0.15    ,0.03"              
"19900820   ,-2.17    ,0.39    ,0.31    ,0.03"              
"19900821   ,-2.56    ,0.88    ,0.59    ,0.03"              
"19900822    ,0.25   ,-0.85    ,0.26    ,0.03"              
"19900823   ,-1.39    ,0.48   ,-0.41    ,0.03"              
"19900824    ,0.35   ,-1.21    ,0.25    ,0.03"              
"19900827    ,2.76   ,-1.58    ,0.43    ,0.03"              
"19900828    ,0.61    ,0.47   ,-0.93    ,0.03"              
"19900829    ,0.09   ,-0.40   ,-0.48    ,0.03"              
"19900830   ,-0.09   ,-0.42   ,-0.25    ,0.03"              
"19900831   ,-1.15    ,0.32   ,-0.23    ,0.03"              
"19900903   ,-1.38    ,0.15    ,0.15    ,0.03"              
"19900904   ,-1.03    ,0.54    ,0.12    ,0.03"              
"19900905    ,1.12   ,-0.20   ,-0.06    ,0.03"              
"19900906   ,-0.61    ,0.41    ,0.01    ,0.03"              
"19900907   ,-0.79   ,-0.21   ,-0.04    ,0.03"              
"19900910    ,0.07   ,-0.94   ,-0.06    ,0.03"              
"19900911   ,-0.93    ,0.41   ,-0.02    ,0.03"              
"19900912    ,0.39   ,-0.19    ,0.26    ,0.03"              
"19900913    ,0.45    ,0.35    ,0.00    ,0.03"              
"19900914   ,-0.52    ,0.72   ,-0.03    ,0.03"              
"19900917   ,-0.20    ,0.09   ,-0.04    ,0.03"              
"19900918   ,-0.99    ,0.26    ,0.18    ,0.03"              
"19900919   ,-1.97   ,-0.07   ,-0.01    ,0.03"              
"19900920   ,-2.38    ,0.49   ,-0.41    ,0.03"              
"19900921   ,-1.19   ,-0.82    ,0.23    ,0.03"              
"19900924   ,-0.26    ,0.72    ,0.39    ,0.03"              
"19900925   ,-1.64    ,0.32   ,-0.10    ,0.03"              
"19900926   ,-0.19   ,-0.28   ,-0.09    ,0.03"              
"19900927    ,0.33   ,-0.17    ,0.24    ,0.03"              
"19900928   ,-1.08    ,0.66   ,-0.10    ,0.03"              
"19901001    ,2.66   ,-2.07   ,-0.22    ,0.03"              
"19901002    ,1.83   ,-0.95   ,-0.25    ,0.03"              
"19901003    ,0.88   ,-0.06    ,0.00    ,0.03"              
"19901004    ,0.00    ,0.51    ,0.03    ,0.03"              
"19901005    ,0.94   ,-0.55   ,-0.24    ,0.03"              
"19901008    ,2.97   ,-0.06    ,0.20    ,0.03"              
"19901009   ,-1.11    ,0.65   ,-0.04    ,0.03"              
"19901010   ,-1.26    ,0.72   ,-0.02    ,0.03"              
"19901011    ,0.22   ,-0.44   ,-0.47    ,0.03"              
"19901012    ,0.46   ,-0.26    ,0.35    ,0.03"              
"19901015   ,-0.25   ,-0.28   ,-0.17    ,0.03"              
"19901016    ,0.46    ,0.45    ,0.03    ,0.03"              
"19901017   ,-0.54    ,0.22    ,0.10    ,0.03"              
"19901018    ,1.08   ,-0.50   ,-0.10    ,0.03"              
"19901019    ,0.39   ,-0.78   ,-0.05    ,0.03"              
"19901022   ,-0.34   ,-0.18   ,-0.02    ,0.03"              
"19901023    ,1.21   ,-0.91    ,0.12    ,0.03"              
"19901024    ,0.34    ,0.47    ,0.21    ,0.03"              
"19901025   ,-0.66    ,0.55   ,-0.07    ,0.03"              
"19901026   ,-1.45    ,0.74   ,-0.34    ,0.03"              
"19901029   ,-0.61   ,-0.03    ,0.10    ,0.03"              
"19901030   ,-0.86    ,0.62   ,-0.16    ,0.03"              
"19901031    ,0.02   ,-0.51    ,0.11    ,0.03"              
"19901101   ,-0.27    ,0.35    ,0.07    ,0.03"              
"19901102    ,0.23   ,-0.12    ,0.02    ,0.03"              
"19901105    ,0.98   ,-0.32   ,-0.20    ,0.03"              
"19901106    ,0.06   ,-0.23    ,0.19    ,0.03"              
"19901107   ,-0.62    ,0.51    ,0.11    ,0.03"              
"19901108   ,-1.08    ,0.31   ,-0.03    ,0.03"              
"19901109    ,0.17   ,-0.42    ,0.12    ,0.03"              
"19901112    ,0.68   ,-0.80   ,-0.06    ,0.03"              
"19901113    ,0.33   ,-0.32    ,0.05    ,0.03"              
end

drop in 1/3 
gen date = daily(word(v1, 1), "YMD")
format date %td 
split v1, destring p(,)
drop v1 v11 
rename (v*) (Mkt_RF SMB HML R) 
foreach v of var * { 
    replace `v' = . if `v' < -99 
}

list in 1/20 


     +----------------------------------------+
     |      date   Mkt_RF    SMB    HML     R |
     |----------------------------------------|
  1. | 02jul1990      .99    .05   -.53   .03 |
  2. | 03jul1990      .33   -.12   -.03   .03 |
  3. | 04jul1990      .24   -.01   -.14   .03 |
  4. | 05jul1990     -.64    .19    .06   .03 |
  5. | 06jul1990      .08   -.19    .14   .03 |
     |----------------------------------------|
  6. | 09jul1990      .49    .03   -.27   .03 |
  7. | 10jul1990      .01    .36   -.39   .03 |
  8. | 11jul1990     -.34   -.49   -.05   .03 |
  9. | 12jul1990      .61   -.16   -.02   .03 |
 10. | 13jul1990      .48   -.61    .03   .03 |
     |----------------------------------------|
 11. | 16jul1990      .31   -.21   -.22   .03 |
 12. | 17jul1990      .55    .15    .26   .03 |
 13. | 18jul1990      .12   -.08   -.03   .03 |
 14. | 19jul1990     -.13     .3   -.19   .03 |
 15. | 20jul1990      .34    -.1    .11   .03 |
     |----------------------------------------|
 16. | 23jul1990     -.07    .66   -.01   .03 |
 17. | 24jul1990     -.38      0    .17   .03 |
 18. | 25jul1990      .15   -.19    .28   .03 |
 19. | 26jul1990      -.3    .35   -.13   .03 |
 20. | 27jul1990      .21    .49   -.24   .03 |
     +----------------------------------------+

[Robert Prins]

Thank you very much Nick, this worked perfectly!