R语言金融分析与建模：R和Excel的交互

[人邮异步社区]

迄今为止，对大多数商学校的本科教育而言，Excel在大多数金融建模课程中非常流行。相当多的教程用Excel作为唯一的计算工具。由于R可以作为下一代金融建模课程的主要计算工具，我们需要讲解R与Excel之间的互动，即我们需要建立一个桥梁将它们相互连接。简言之，在本章中我们讨论若干种在R与Excel之间传输数据的方法。

17.1　安装与Excel相关的R包

在本章中，我们将介绍和使用与Excel相关联的3个R包中包含的几个函数。这3个R包为readxl、writexl和openxlsx。要安装这些软件包，可以以下代码。注意当我们安装每个包时，需要使用双引号（或单引号）。

> install.packages("readxl")
> install.packages("writexl")
> install.packages("openxlsx")

但是，当上传这些软件包，我们可以使用library()或require()函数。函数的输入包可带或不带引号，这里我们以readxl为例，详见下面语句。

>library(readxl)
>library("readxl")
>require(readxl)
>require("readxl")

表17.1列出一些与Excel有关的R包。

表17.1　与Excel有关的R包

R包	用途
readxl	读取Excel文件
writexl	写入Excel文件
openxlsx	读取、写入、编辑Excel文件

17.2　与Excel相关的R包手册

在安装好R包后，最佳的学习资料是用该包的参考手册和vignettes。对于readxl，我们可以打开readxl包的主页，就会看到相关的手册。为节省篇幅，我们只显示vignette的一小部分，如图17-1所示。

 

图17-1　R包参考手册和vignettes

第二种方法是先找到在自己计算机上与某包相关的位置。安装完一个软件包后，你已经将文档保存在本地计算机上。以下是查找与readxl相关信息的示例。首先，我们试图找到readxl模块在我们计算机上的目录。

> path.package('readxl', quiet = FALSE)
[1] "C:/Users/yany/Documents/R/win-library/3.3/XLConnect"

要注意的是，对不同的读者，目录大不相同。找到特定的目录后，我们会有以下内容，如图17-2所示。

 

图17-2　文件存储目录

单击不同的子目录，我们会发现更多的有用的内容。表17.2给出几个子目录的内容。

表17.2　几个子目录及它们包含的文件

子目录	内容
doc
extdata
help
meta

17.3　通过剪贴板将数据写到Excel

剪贴板对应的英文为clipboard。例如read.table(“clipboard”)或write.table(“clipboard”)。下面我们首先生成

 

矩阵。然后将

 

写在剪贴板上。

> x<-1:100
> y<-matrix(x,20,5,byrow=T)
> head(y,2)
     [,1] [,2] [,3] [,4] [,5]
[1,]    1    2    3    4    5
[2,]    6    7    8    9   10
> write.table(y,'clipboard')

打开Excel表格，粘贴在适当位置，如图17-3所示。

 

图17-3　在Excel文件中粘贴数据

从以上R语句，我们知道

 

变量应有5列。但在Excel中只有一列。为此，我们可以用Excel“分列”将其分开。具体操作如下：复制该列，单击“数据”，然后单击“分列”。然后用空格作为分隔符。

17.4　通过剪贴板将Excel数据读入R

假设我们有图17-4所示的Excel数据。

 

图17-4　Excel文件中的数据

为将数据输入到R中，我们首先复制该Excel数据。假设我们想将用这些值为x赋值。赋值语句为read.table("clipboard")。

>x<-read.table("clipboard")
> x
V1 V2
1 1 2
2 3 1
3 -1 5

如果Excel数据集有一个标题（列名），我们添加header=T。在我们复制之前，请记住如果命令中有header=T，就必须同时拷贝标题和数据，如图17-5所示。

 

图17-5　同时复制标题和数据

相应的语句与结果如下。

> x<-read.table("clipboard",header=T)
> x
date ret
1 20010203 0.01
2 20010204 -0.02
3 20010205 0.05

相对较小数据集而言，以上方法简单适用。用户只需输入几个数据值即可对其进行测试。但对于较大的数据集，较为稳妥的方法是先从Excel中输出数据集，如CSV（逗号分隔值）文件。然后使用read.table()或read.csv()函数读入该数据集。

17.5　read.table()函数和read.csv()函数

假设我们已经有数据，其文件名为test.csv。该文件位于datas/。以下两行R代码可用于读取该数据集。

> x<-read.csv("datas/test.csv",header=T)           
> x<-read.table("datas/test.csv",header=T, sep=',')

17.6　read.xlsx()函数

与该函数相关的软件包称为openxlsx。如果我们在Excel中有一个非常简单的数据集，称为test.xlsx，如图17-6所示。

 

图17-6　Excel中的数据集

我们可以用两行R代码来阅读它。要注意的是，对第二条语句而言，最后一次输入变量的赋值为1，即第一个Excel表格。

> library(openxlsx)
> x <- read.xlsx("datas/test.xlsx", sheet=1)
> x
date ret
1 20010203 0.01
2 20010204 -0.02
3 20010205 0.05

如果我们知道Excel表格的名称，就可以使用该名称。例如名称为Sheet1，那么我们就用sheet='Sheet1'。

> x <- read.xlsx("datas/test.xlsx", sheet="Sheet1")

我们可以使用以下几组命令来检索openxlsx包中包含的数据。

> f <- system.file("extdata/readTest.xlsx", package ="openxlsx")
> x <- read.xlsx(f, sheet=1)
> head(x)
Var1 Var2 Var3 Var4  Var5         Var6 Var7
1  TRUE    1 1.00    a 42042 3209324 This   NA
2  TRUE   NA   NA    b 42041         <NA>   NA
3  TRUE    2 1.34    c 42040         <NA>   NA
4 FALSE    2   NA <NA>    NA         <NA>   NA
5 FALSE    3 1.56    e    NA         <NA>   NA
6 FALSE    1 1.70    f 42037         <NA>   NA

使用openxlsx包中包含的数据集非常方便，包含在该R包中有8个演示数据集，请参阅表17.3。

表17.3　包含在openxlsx包中的数据集列表

数据	含义
cloneEmptyWorksheetExample.xlsx	空表
cloneWorksheetExample.xlsx	只有第一个单元格有数值1
groupTest.xlsx
loadExample.xlsx	若干数据集：iris, mtcars等及其透视表, 统计图
namedRegions.xlsx
namedRegions2.xlsx
read_failure_test.xlsx
readTest.xlsx	各种类型的表格示例数据

其次，直接打开这些Excel数据集也是一个绝妙的主意。为了找到这些Excel文件的位置，我们可以使用path.package()函数。

> path.package('openxlsx')
[1] "D:/R-4.0.3/library/openxlsx"

要注意的是，不同的读者会得到不同的途径。根据上述路径，我们会有图17-7所示的目录。我们可以在extdata文件夹里找到所有相关演示数据集，如图17-8所示。

 

图17-7　文件存储目录

 

图17-8　演示数据集

这是找到openxlsx子目录下的所有示例Excel文件的好方法。请注意，如果我们在命令中使用pattern='\.xlsx$'，我们会遗漏扩展名为.xls的文件。

path.package('openxlsx')
x <- path.package('openxlsx')
y <- list.files(x, recursive = T, pattern='\\.xlsx$')
length(y)
[1] 8

总共有8个样本Excel程序。

> y
[1] "extdata/cloneEmptyWorksheetExample.xlsx"
[2] "extdata/cloneWorksheetExample.xlsx"     
[3] "extdata/groupTest.xlsx"                 
[4] "extdata/loadExample.xlsx"               
[5] "extdata/namedRegions.xlsx"              
[6] "extdata/namedRegions2.xlsx"             
[7] "extdata/read_failure_test.xlsx"         
[8] "extdata/readTest.xlsx" "

17.7　read_xlsx函数

read_xlsx函数是包含在readxl R包中的，详见下面例子。

> library(readxl)
> data <- readxl_example("datasets.xlsx")
> x<-read_xlsx(data)
> dim(x)
[1] 150   5
> head(x,2)
# A tibble: 2 x 5
  Sepal.Length Sepal.Width Petal.Length Petal.Width Species
         <dbl>       <dbl>        <dbl>       <dbl>   <chr>
1          5.1         3.5          1.4         0.2  setosa
2          4.9         3.0          1.4         0.2  setosa

17.8　system.file()函数

要找出这些系统文件的具体位置，可以使用以下两行R代码。

> system.file("extdata/datasets.xlsx", package="readxl")
[1] "D:/R-4.0.3/library/readxl/extdata/datasets.xlsx"

17.9　read_excel()函数

以下是readxl包的read_excel()函数的一般格式。

read_excel(path, sheet = NULL, range = NULL, col_names = TRUE,
  col_types = NULL, na = "", trim_ws = TRUE, skip = 0,
  n_max = Inf, guess_max = min(1000, n_max),
  progress = readxl_progress(), .name_repair = "unique")

首先我们找到在extdata下的一些Excel文件名。

> library(readxl)
> path.package("readxl")
[1] "D:/R-4.0.3/library/readxl"

要注意的是，不同的读者会有不同的途径。从extdata目录下，我们可以找到以下Excel文件，如图17-9所示。

 

图17-9　extdata文件夹里的Excel文件

例如对Excel数据文件datasets.xlsx而言，第一个文件名为“iris”，其有图17-10所示的数据。

 

图17-10　datasets.xlsx文件中的数据

如果我们打算将B4至E10区间的数据读入，可使用以下代码。

> f <- system.file("extdata/datasets.xlsx",package="readxl")
> x <- read_excel(f, sheet=1, range="B4:E10", col_names=F)
> x
  # A tibble: 7 x 4
   ...1  ...2  ...3 ...4  
  <dbl> <dbl> <dbl> <chr> 
1   3.2   1.3   0.2 setosa
2   3.1   1.5   0.2 setosa
3   3.6   1.4   0.2 setosa
4   3.9   1.7   0.4 setosa
5   3.4   1.4   0.3 setosa
6   3.4   1.5   0.2 setosa
7   2.9   1.4   0.2 setosa

17.10　write_xlsx()函数

该函数包含在writexl的R包中。

load("datas/ff3monthly.rda")
head(ff3monthly, 2)
        DATE  MKT_RF     SMB     HML     RF
1 1926-07-01  0.0296 -0.0230 -0.0287 0.0022
2 1926-08-01  0.0264 -0.0140  0.0419 0.0025
> library(writexl)
> write_xlsx(ff3monthly, "datas/ff3.xlsx", col_names=T,
           format_headers=T)

输出的Excel文件的前几行显示如图17-11所示。

 

图17-11　Excel文件的前几行

17.11　相关的例子及数据集

我们可以用以下语句找到R演示程序。从下面结果可以看出，我们有13个R程序。

> library(openxlsx)
> path <- path.package('openxlsx')
> demo <- "/extdata"
> path2 <- paste(path, demo, sep='')
> dir(path2, pattern='\\.R')
[1] "build_font_size_lookup.R"        
[2] "conditional_formatting_testing.R"
[3] "load_xlsx_testing.R"             
[4] "stack_style_testing.R"

同理，我们可以找到有关的R数据集。

> MetaData <- "/Meta"
> path3 <- paste(path, MetaData, sep='')
> dir(path3)
[1] "features.rds" "hsearch.rds"  "links.rds"    "nsInfo.rds"  
[5] "package.rds"  "Rd.rds"       "vignette.rds"
>
  
  

本文摘自：《R语言金融分析与建模》

 

R既是统计、挖掘、计算、分析、制图等方面的工具，也是一个强大的开发与应用平台。几乎任何与数据相关的难题，都可以借助R语言来解决。而金融领域正是与数据密切相关的行业，可以通过R实现量化金融分析与建模。

本书系统地介绍了R的包与编程方法，并通过丰富的金融案例展示了R在金融分析和金融建模方面的应用。本书分为5篇，共30章，从R语言基础、金融模型及基础知识、数据及相关操作、R在金融建模中的应用和R高级技能几个主题出发，全面讲解了R在金融量化中的应用和技巧。

本书适合从事金融数据分析、金融量化建模的读者学习。通过阅读本书，读者将了解全球化的金融市场数据，学习多样的金融建模思想和解决方案。