.chapter12<-function(i=0){ " i Chapter 12: Excel and R i Explanations - ---------------------------- -- ------------------------- 1 R and Excel 21 readxl: package 2 Excel files vs. CSV files 22 : sheet names 3 Strategies dealing with Excel files 23 : all system Excel files 4 Replace comma with semi-colon 24 : read a system Excel file 5 Get packages with keyword 'Excel' 25 : read a remote file 6 List of our Excel files 26 : sheet names 7 download.file() function 27 : path of system Excel files 8 openxlsx : read.xlsx 28 : Method II: show all system Excel files 9 : format 29 write.table(x,'clipboard') 10 : system Excel files 30 write('clipboard') 11 : read a system Excel file 31 no data sets found 12 : get sheet names 32 error XLConnet 13 : different sheets 33 lowercase issue 14 : data_excel files 34 New Excel-related package 15 : Remote Excel 35 : steps 16 : write to excel 36 : getting functions 17 : help(package=openxlsx) 37 : list of functions 18 read.csv('clipboard') 38 apropos('excel') 19 Videos 39 : .data2excel 20 Links 40 : .data2Excel_Mac Example #1:>.c12 # find out the list Example #2:>.c12() # the same as the above Example #3:>.c12(1) # see the first explanation ";.zchapter12(i)} .n12chapter<-40 .zchapter12<-function(i){ if(i==0){ print(.c12) }else{ if(i<=.n12chapter){ .printEachQ(12,i,.n12chapter) }else{ cat("Invalid number: input value should between 1 and ",.n12chapter,"\n") } } } .c12<-.chapter12 .C12EXPLAIN1<-"R and Excel //////////////////////////// R and Excel are powerful tools that complement each other in the realm of data analysis and manipulation. While Excel is widely recognized for its spreadsheet-based calculations and user-friendly interface, R Excel in statistical analysis, data visualization, and advanced programming capabilities. The integration between R and Excel provides users with a seamless workflow, allowing them to leverage the strengths of both platforms. Analysts often use Excel for data entry, cleaning, and basic calculations before harnessing R's statistical functions and sophisticated plotting libraries for in-depth analysis. Various packages, such as \"openxlsx\" and \"readxl\" facilitate the import and export of data between R and Excel, enabling users to maintain the convenience of Excel for certain tasks while harnessing the analytical power of R for more complex statistical modeling and visualization. This interoperability makes R and Excel a dynamic duo for professionals who seek the best of both worlds in terms of data manipulation, analysis, and reporting. //////////////////////////// " .C12EXPLAIN2<-"Excel files vs. CSV files //////////////////////////// CSV stands for comma-separated values Excel files have an extension of xlsx or xlx while CSV files have an extension of .csv. For a CSV file, we can use the read.csv() or read.table() functions. Thus, this would be one solutio for an Excel if 1) the size of the Excel file is samll 2) just one or few sheets 3) no or few commas Then, we can replace commas with space or ; then export our data to a CSV file. The read.csv() or read.table() functions can be used to retrieve them. //////////////////////////// " .C12EXPLAIN3<-"Strategies for Excel files //////////////////////////// To deal with Excel files, we recommend the following four strategies. Strategy #1: Seeing is believing; launch your Excel file and look at the data sets for the header, starting cell, any missing code, number of sheets, and the first few observations. Note that if saving your data as a CSV file, consider the impact of the commas inside your data. Strategy #2: for a small data set, launch your Excel and export or save your data set as a CSV file. Then, use the read.csv() or read.table() functions to retrieve the data. Strategy #3: Focusing on one R package for Excel files. If this one is not functioning correctly, switch to another package. Strategy #4: Always compare your inputted data (using R) with your raw data, such as the number of observations, the first few lines, and maximum, mean, and minimum values. Strategy #5: If your function does not support reading a remote file, then download it first. download.file(remoteExcelFile,outfile,'wb') //////////////////////////// " .C12EXPLAIN4<-"Replacing comma with semi-colon //////////////////////////// For a small data set contained within an Excel file, we can export it to a CSV file. However, when comma is part of data, we have to replace them with another symbol such as a semi-colon. //////////////////////////// " .C12EXPLAIN5<-"Excel related R packages //////////////////////////// loc<-\"http://cran.us.r-project.org\" x<-available.packages(repos=loc) df<-data.frame(x) > grep(\"EXCEL\",toupper(df$Package)) [1] 5237 5238 5239 5240 df[grep(\"EXCEL\",toupper(df$Package)),1] [1] \"excel.link\" \"exceldata\" \"ExcelFunctionsR\" \"excelR\" //////////////////////////// " .C12EXPLAIN6<-"List of our Excel files //////////////////////////////////////////////// infile<-'http://datayyy.com/data_excel/list.txt' x<-readLines(infile) length(x) # [1] 50 head(x,10) [1] \"amexCap.xlsx\" [2] \"benford.xlsx\" [3] \"big-mac-2018-07-01.xls\" [4] \"big-mac-2019-01-01.xls\" [5] \"big-mac-2019-07-09.xls\" [6] \"big-mac-2020-01-14.xls\" [7] \"big-mac-2020-07-01.xls\" [8] \"big-mac-2021-01-01.xls\" [9] \"BS_ibm.xlsx\" [10] \"chapter12.txt\" //////////////////////////////////////////////// " .C12EXPLAIN7<-"download.file() function //////////////////////////////////////////////// infile<-\"http://datayyy.com/data_excel/ibmDaily.xlsx\" outfile<-\"a.xlsx\" # test the difference between the following two lines. download.file(infile,outfile) download.file(infile,outfile,mode=\"wb\") //////////////////////////////////////////////// " .C12EXPLAIN8<-"read.xlsx //////////////////////////// library(openxlsx) infile<-\"https://github.com/awalker89/openxlsx/raw/master/inst/readTest.xlsx\" df<-read.xlsx(infile) typeof(df) # [1] \"list\" class(x) # [1] \"data.frame\" > head(df) Var1 Var2 Var3 Var4 Var5 Var6 Var7 1 TRUE 1 1.00 a 42042 3209324 This NA 2 TRUE NA NA b 42041 NA 3 TRUE 2 1.34 c 42040 NA 4 FALSE 2 NA NA NA 5 FALSE 3 1.56 e NA NA 6 FALSE 1 1.70 f 42037 NA > dim(df) [1] 10 7 //////////////////////////// " .C12EXPLAIN9<-"Structure of read.xlsx() function //////////////////////////// read.xlsx( xlsxFile, sheet, startRow = 1, colNames = TRUE, rowNames = FALSE, detectDates = FALSE, skipEmptyRows = TRUE, skipEmptyCols = TRUE, rows = NULL, cols = NULL, check.names = FALSE, sep.names = \".\", namedRegion = NULL, na.strings = \"NA\", fillMergedCells = FALSE ) //////////////////////////// " .C12EXPLAIN10<-"Find out all the system files //////////////////////////// >a<-system.file(package=\"openxlsx\") >a # [1] \"C:/Users/Paul Yan/AppData/Local/R/win-library/4.3/openxlsx\" >setwd(a) > dir() [1] \" DESCRIPTION\" \" doc\" \" extdata\" \" help\" [5] \" html\" \" INDEX\" \" libs\" \" LICENSE\" [9] \" MD5\" \" Meta\" \" NAMESPACE\" \" NEWS.md\" [13] \" R\" \" WORDLIST\" > dir(\"extdata/\") [1] \" ~$loadExample.xlsx\" \" build_font_size_lookup.R\" \" cloneEmptyWorksheetExample.xlsx\" [4] \" cloneWorksheetExample.xlsx\" \" ColorTabs3.xlsx\" \" conditional_formatting_testing.R\" [7] \" einstein.jpg\" \" groupTest.xlsx\" \" inlineStr.xlsx\" [10] \" load_xlsx_testing.R\" \" loadExample.xlsx\" \" loadPivotTables.xlsx\" [13] \" loadThreadComment.xlsx\" \" namedRegions.xlsx\" \" namedRegions2.xlsx\" [16] \" namedRegions3.xlsx\" \" read_failure_test.xlsx\" \" readTest.xlsx\" [19] \" stack_style_testing.R\" > > dir(\"extdata/\",pattern=\"xlsx\") # if Excel files only [1] \" ~$loadExample.xlsx\" \" cloneEmptyWorksheetExample.xlsx\" \" cloneWorksheetExample.xlsx\" [4] \" ColorTabs3.xlsx\" \" groupTest.xlsx\" \" inlineStr.xlsx\" [7] \" load_xlsx_testing.R\" \" loadExample.xlsx\" \" loadPivotTables.xlsx\" [10] \" loadThreadComment.xlsx\" \" namedRegions.xlsx\" \" namedRegions2.xlsx\" [13] \" namedRegions3.xlsx\" \" read_failure_test.xlsx\" \" readTest.xlsx\" //////////////////////////// " .C12EXPLAIN11<-"read one system Excel file //////////////////////////// library(openxlsx) x <- system.file(\"extdata\", \"readTest.xlsx\", package =\"openxlsx\") x [1] \"C:/Users/Paul Yan/AppData/Local/R/win-library/4.3/openxlsx/extdata/readTest.xlsx\" //////////////////////////// " .C12EXPLAIN12<-"Sheet names //////////////////////////// library(openxlsx) # infile<- \"path/to/your/excel_file.xlsx\" infile <- system.file(\"extdata\" ,\"readTest.xlsx\",package =\"openxlsx\") sheet_names <- getSheetNames(infile) print(sheet_names) [1] \"Sheet1\" \"Sheet2\" \"Sheet 3\" \"Sheet 4\" \"Sheet 5\" \"Sheet 6\" \"1\" [8] \"11\" \"111\" \"1111\" \"11111\" \"111111\" //////////////////////////// " .C12EXPLAIN13<-"Reading different sheets //////////////////////////// library(openxlsx) infile <- system.file(\" extdata\" , \" readTest.xlsx\" , package = \" openxlsx\" ) df1 <- read.xlsx(xlsxFile = xlsxFile, sheet = 1, skipEmptyRows = FALSE) head(df1) df2 <- read.xlsx(xlsxFile = xlsxFile, sheet = 3, skipEmptyRows = TRUE) df2$Date <- convertToDate(df2$Date) head(df2) //////////////////////////// " .C12EXPLAIN14<-"all system Excel files //////////////////////////// loc<-'http://datayyy.com/data_excel/' x<-readLines(loc) x2<-x[grep(\".xlsx\",x)] x3<-sub(\".xlsx.*\",\".xlsx\",x2) x4<-sub(\".*sort=\\\"\",\"\",x3) > x4 [1] \"amexcap.xlsx\" \"benford.xlsx\" \"bs_ibm.xlsx\" [4] \"chickeneggs.xlsx\" \"countryrisk2.xlsx\" \"cpimonthly_cpiaucsl.xlsx\" [7] \"datacase5.xlsx\" \"datamonthly.xlsx\" \"dollarindexweights.xlsx\" [10] \"eventstudy.xlsx\" \"ex16_4.xlsx\" \"ex2_canus_usuk.xlsx\" [13] \"exam2021f.xlsx\" \"excaus_exuseu.xlsx\" \"exhibit16_4.xlsx\" [16] \"f311hw3q4.xlsx\" \"ff3_ibm5years.xlsx\" \"ff3_ibmmonthly.xlsx\" [19] \"ff3monthly.xlsx\" \"fin508datacase1.xlsx\" \"final2.xlsx\" [22] \"gands.xlsx\" \"googlescholargeneseo.xlsx\" \"gs2schools.xlsx\" [25] \"ibm.xlsx\" \"ibmdaily.xlsx\" \"ibmis_bs.xlsx\" [28] \"ibmmonthly.xlsx\" \"is_ibm.xlsx\" \"jnj.xlsx\" [31] \"kuiper.xlsx\" \"multidata3.xlsx\" \"nstock_stdport.xlsx\" [34] \"nysecap.xlsx\" \"nyselisting.xlsx\" \"sp500annual.xlsx\" [37] \"sp500constituents.xlsx\" \"sp500monthly.xlsx\" \"wmtmonthly.xlsx\" some issues with capitalization amexcap.xlsx should be amexCap.xlsx check http://datayyy.com/data_excel/list.txt //////////////////////////// " .C12EXPLAIN15<-"Reading a remote Excel file //////////////////////////// library(openxlsx) infile<-'http://datayyy.com/data_excel/amexCap.xlsx' df <- read.xlsx(xlsxFile =infile) head(df) > head(df) Symbol Name MarketCap Exchange 1 AA-P Alcoa Inc. 43.6 AMEX 2 AAC Ableauctions.Com Inc 4.3 AMEX 3 AAU Almaden Minerals; Ltd. 89.4 AMEX 4 ABL American Biltrite Inc. 19.1 AMEX 5 ABP Abraxas Petroleum Corporation 219.0 AMEX 6 ACU Acme United Corporation. 46.9 AMEX > //////////////////////////// " .C12EXPLAIN16<-"write to an Excel file //////////////////////////// library(openxlsx) data <- data.frame( Name = c(\"John\", \"Jane\", \"Bob\"), Age = c(25, 30, 22) ) write.xlsx(data,\"c://temp/test.xlsx\") //////////////////////////// " .C12EXPLAIN17<-"help(package=openxlsx) //////////////////////////// help(package=openxlsx) Documentation for package ‘openxlsx’ version 4.2.5.2 DESCRIPTION file. User guides, package vignettes and other documentation. Package NEWS. Help Pages %||% If NULL then ... activeSheet Get/set active sheet of the workbook activeSheet<- Get/set active sheet of the workbook addCreator Add another author to the meta data of the file. addFilter Add column filters addStyle Add a style to a set of cells addWorksheet Add a worksheet to a workbook all.equal Check equality of workbooks all.equal.Workbook Check equality of workbooks buildWorkbook Build Workbook cloneWorksheet Clone a worksheet to a workbook col2int Convert Excel column to integer conditionalFormat Add conditional formatting to cells conditionalFormatting Add conditional formatting to cells convertFromExcelRef Convert excel column name to integer index convertToDate Convert from excel date number to R Date type convertToDateTime Convert from excel time number to R POSIXct type. copyWorkbook Copy a Workbook object. createComment create a Comment object createNamedRegion Create / delete a named region. createStyle Create a cell style createWorkbook Create a new Workbook object databar Add conditional formatting to cells dataValidation Add data validation to cells deleteData Delete cell data deleteNamedRegion Create / delete a named region. freezePane Freeze a worksheet pane getBaseFont Return the workbook default font getCellRefs Return excel cell coordinates from (x,y) coordinates getCreators Add another author to the meta data of the file. getDateOrigin Get the date origin an xlsx file is using getNamedRegions Get named regions getSheetNames Get names of worksheets getStyles Returns a list of all styles in the workbook getTables List Excel tables in a workbook groupColumns Group columns groupRows Group Rows if_null_then If NULL then ... insertImage Insert an image into a worksheet insertPlot Insert the current plot into a worksheet int2col Convert integer to Excel column loadWorkbook Load an existing .xlsx file makeHyperlinkString create Excel hyperlink string mergeCells Merge cells within a worksheet modifyBaseFont Modify the default font names get or set worksheet names names.Workbook get or set worksheet names names<-.Workbook get or set worksheet names op.openxlsx openxlsx Options openXL Open a Microsoft Excel file (xls/xlsx) or an openxlsx Workbook openxlsx xlsx reading, writing and editing. openxlsxFontSizeLookupTable Font Size Lookup tables openxlsxFontSizeLookupTableBold Font Size Lookup tables openxlsx_getOp openxlsx Options openxlsx_options openxlsx Options openxlsx_setOp openxlsx Options pageBreak add a page break to a worksheet pageSetup Set page margins, orientation and print scaling protectWorkbook Protect a workbook from modifications protectWorksheet Protect a worksheet from modifications read.xlsx Read from an Excel file or Workbook object readWorkbook Read from an Excel file or Workbook object removeCellMerge Create a new Workbook object removeColWidths Remove column widths from a worksheet removeComment Remove a comment from a cell removeFilter Remove a worksheet filter removeRowHeights Remove custom row heights from a worksheet removeTable Remove an Excel table in a workbook removeWorksheet Remove a worksheet from a workbook renameWorksheet Rename a worksheet replaceStyle Replace an existing cell style saveWorkbook save Workbook to file setColWidths Set worksheet column widths setFooter Set footer for all worksheets setHeader Set header for all worksheets setHeaderFooter Set document headers and footers setLastModifiedBy Add another author to the meta data of the file. setRowHeights Set worksheet row heights sheets Returns names of worksheets. sheetVisibility Get/set worksheet visible state sheetVisibility<- Get/set worksheet visible state sheetVisible Get worksheet visible state. sheetVisible<- Get worksheet visible state. showGridLines Set worksheet gridlines to show or hide. temp_xlsx helper function to create tempory directory for testing purpose ungroupColumns Ungroup Columns ungroupRows Ungroup Rows worksheetOrder Order of worksheets in xlsx file worksheetOrder<- Order of worksheets in xlsx file write.xlsx write data to an xlsx file writeComment write a cell comment writeData Write an object to a worksheet writeDataTable Write to a worksheet as an Excel table writeFormula Write a character vector as an Excel Formula //////////////////////////// " .C12EXPLAIN18<-" read.csv('clipboard') //////////////////////////// For a small Excel data set Step 1: Highlight and copy Step 2: issue the following x<- read.csv('clipboard') //////////////////////////// " .C12EXPLAIN19<-"Videos //////////////////////////////////////////////// Dynamic Data Script, 2022, How to import data from Excel files to R | R Programming (t6:30) https://www.youtube.com/watch?v=q66Gk-DPBh4 R Programming 101,2020, How to import data from excel into R studio. R programming for beginners (t8:01) https://www.youtube.com/watch?v=cnD1op2Oo3M&t=10s //////////////////////////////////////////////// " .C12EXPLAIN20<-"Links //////////////////////////////////////////////// Joubert, Shayna, 2019, R vs. Excel: What’s the Difference? https://graduate.northeastern.edu/resources/r-vs-excel/ R Trader, 2014, A million ways to connect R and Excel https://www.r-bloggers.com/2014/02/a-million-ways-to-connect-r-and-excel/ Radecic, Dario, 2022, R and Excel: How to Combine the Best of Both Worlds https://appsilon.com/r-and-excel/ Yan, Yuxing, 2024, a list of Excel files datayyy.com/data_excel/list.txt. Yan, Yuxing, 2024, a list of CSV files datayyy.com/data_csv/list.txt. //////////////////////////////////////////////// " .C12EXPLAIN21<-"readxl //////////////////////////// library(readxl) my_data <- read_excel(\"input_file.xlsx\") //////////////////////////// " .C12EXPLAIN22<-"Getting sheet names //////////////////////////// library(readxl) #infile<- \" path/to/your/excel_file.xlsx\" infile <- system.file(\" extdata\" ,\" readTest.xlsx\" ,package =\" openxlsx\" ) sheet_names <- excel_sheets(infile) > print(sheet_names) [1] \" Sheet1\" \" Sheet2\" \" Sheet 3\" \" Sheet 4\" \" Sheet 5\" \" Sheet 6\" [7] \" 1\" \" 11\" \" 111\" \" 1111\" \" 11111\" \" 111111\" //////////////////////////// " .C12EXPLAIN23<-" system files for readxl packages //////////////////////////// library(readxl) myDir<-system.file(package =\"readxl\") myDir<- [1] \"C:/Users/pauly/AppData/Local/R/win-library/4.3/readxl\" > setwd(myDir) > dir() [1] \"DESCRIPTION\" \"doc\" \"extdata\" \"help\" \"html\" \"INDEX\" \"libs\" [8] \"LICENSE\" \"MD5\" \"Meta\" \"NAMESPACE\" \"NEWS.md\" \"R\" \"WORDLIST\" > dir() [1] \"DESCRIPTION\" \"doc\" \"extdata\" \"help\" \"html\" \"INDEX\" [7] \"libs\" \"LICENSE\" \"MD5\" \"Meta\" \"NAMESPACE\" \"NEWS.md\" [13] \"R\" \"WORDLIST\" > > dir(\"extdata/\") [1] \"clippy.xls\" \"clippy.xlsx\" \"datasets.xls\" \"datasets.xlsx\" [5] \"deaths.xls\" \"deaths.xlsx\" \"geometry.xls\" \"geometry.xlsx\" [9] \"type-me.xls\" \"type-me.xlsx\" //////////////////////////// " .C12EXPLAIN24<-"read a system Excel file //////////////////////////// library(readxl) myDir<-system.file(package =\"readxl\") myDir2<-paste0(myDir,\"/extdata\") setwd(myDir2) files<-dir() files [1] \"clippy.xls\" \"clippy.xlsx\" \"datasets.xls\" [4] \"datasets.xlsx\" \"deaths.xls\" \"deaths.xlsx\" [7] \"geometry.xls\" \"geometry.xlsx\" \"type-me.xls\" [10] \"type-me.xlsx\" x<-read_excel(files[1]) //////////////////////////// " .C12EXPLAIN25<-"read a remote Excel file //////////////////////////// library(readxl) infile<-\"http://datayyy.com/data_excel/ibmDaily.xlsx\" x<-read_excel(infile) Error: `path` does not exist: 'http://datayyy.com/data_excel/ibmDaily.xlsx' setwd('e://') outfile<-\"a.xlsx\" download.file(infile,outfile,mode=\"wb\") x<-read_excel(outfile) > head(x) # A tibble: 6 × 7 Date Open High Low Close `Adj Close` Volume 1 1962-01-02 00:00:00 7.37 7.37 7.29 7.29 1.71 407940 2 1962-01-03 00:00:00 7.29 7.36 7.29 7.36 1.73 305955 3 1962-01-04 00:00:00 7.36 7.36 7.28 7.28 1.71 274575 4 1962-01-05 00:00:00 7.27 7.27 7.13 7.14 1.68 384405 5 1962-01-08 00:00:00 7.13 7.13 6.95 7.00 1.65 572685 6 1962-01-09 00:00:00 7.04 7.18 7.04 7.09 1.67 517770 //////////////////////////// " .C12EXPLAIN26<-" Getting various sheet names //////////////////////////// library(readxl) excel_sheets(readxl_example(\"datasets.xlsx\")) [1] \"iris\" \"mtcars\" \"chickwts\" \"quakes\" //////////////////////////// " .C12EXPLAIN27<-"Path of system Excel files //////////////////////////// library(readxl) path <- readxl_example(\"datasets.xls\") path [1] \"C:/Users/Paul Yan/AppData/Local/R/win-library/4.3/readxl/extdata/datasets.xls\" //////////////////////////// " .C12EXPLAIN28<-"Method II: show all the system Excel files //////////////////////////// library(readxl) > readxl_example() [1] \"clippy.xls\" \"clippy.xlsx\" \"datasets.xls\" [4] \"datasets.xlsx\" \"deaths.xls\" \"deaths.xlsx\" [7] \"geometry.xls\" \"geometry.xlsx\" \"type-me.xls\" [10] \"type-me.xlsx\" > //////////////////////////// " .C12EXPLAIN29<-"write.table(x,'clipboard') //////////////////////////// Step 1: generate our data x Step 2: write.table(x,'clipboard') Step 3: launch Excel and paste there //////////////////////////// " .C12EXPLAIN30<-"write(x,'clipboard') //////////////////////////// //////////////////////////// " .C12EXPLAIN31<-"no data sets found //////////////////////////// > data(package='openxlsx') no data sets found # the reason is that it has Excel files instead //////////////////////////// " .C12EXPLAIN32<-"error for require(XLConnect) //////////////////////////// > require(XLConnect) Loading required package: XLConnect Error: package or namespace load failed for 'XLConnect': .onLoad failed in loadNamespace() for 'rJava', details: call: fun(libname, pkgname) error: JAVA_HOME cannot be determined from the Registry //////////////////////////// " .C12EXPLAIN33<-"lower case issues //////////////////////////// when using the R readLines() function to retrieve a website, it returns all the lower case, such as amexCap.xlsx became amexcap.xlsx. How do we solve this problem? library(httr) url <- \"https://datayyy.com/data_excel/\" response <- GET(url) # Check if the request was successful (status code 200) if (http_status(response)$status == 200) { # Read the content as text content <- content(response, as = \"text\") # Now 'content' contains the raw content of the website, and case sensitivity should be preserved. # You can further process or save the content as needed. } else { warning(\"Failed to retrieve the content. HTTP Status Code: \", http_status(response)$status) } //////////////////////////// " .C12EXPLAIN34<-"New Excel-related package //////////////////////////// Step 1: Search the list of R packages to find one Excel-related package Step 2: Use help(package=packageName) to find a set of functions Step 3: Choose a few functions to see their explanations. Step 4: Test a few related examples. //////////////////////////// " .C12EXPLAIN35<-"Related functions //////////////////////////// # Using tidyxl as an example > library(tidyxl) > help(package=tidyxl) //////////////////////////// " .C12EXPLAIN36<-"listed functions //////////////////////////// library(tidyxl) help(package=tidyxl) tidyxl-package tidyxl: Import xlsx (Excel) spreadsheet data and formatting into tidy structures. excel_functions Names of all Excel functions is_date_format Test that Excel number formats are date formats is_range Test that Excel formulas are ranges maybe_xlsx Determine file format tidyxl tidyxl: Import xlsx (Excel) spreadsheet data and formatting into tidy structures. tidy_xlsx Import xlsx (Excel) cell contents into a tidy structure. xlex Parse xlsx (Excel) formulas into tokens xlsx_cells Import xlsx (Excel) cell contents into a tidy structure. xlsx_color_standard Names and RGB values of Excel standard colours xlsx_color_theme Import theme color definitions from xlsx (Excel) files xlsx_colour_standard Names and RGB values of Excel standard colours xlsx_colour_theme Import theme color definitions from xlsx (Excel) files xlsx_formats Import xlsx (Excel) formatting definitions. xlsx_names Import named formulas from xlsx (Excel) files xlsx_sheet_names List sheets in an xlsx (Excel) file xlsx_validation Import data validation rules of cells in xlsx (Excel) files //////////////////////////// " .C12EXPLAIN37<-" //////////////////////////// //////////////////////////// " .C12EXPLAIN38<-" //////////////////////////// source('http://datayyy.com/fmr/week6.txt') > apropos(\"excel\") [1] \".copyFinStatementFromYahoo2Excel\" \".data2excel\" [3] \".data2Excel\" \".data2excel_Mac\" [5] \".data2Excel_Mac\" \".data2excel_Windows\" [7] \".data2Excel_Windows\" \".finS2excel\" [9] \".finS2Excel\" \".image2excel\" [11] \".image2Excel\" \".mimicExcel\" [13] \".mimicexcel_\" \".zcopyFinStatementFromYahoo2Excel\" [15] \".zdata2excel_\" \".zdata2excel_Mac_\" [17] \".zdata2excel_Windows_\" \".zimage2excel\" //////////////////////////// " .C12EXPLAIN39<-".data2Excel //////////////////////////// .data2excel<-function(keyword_or_ID){ \"Objective : keyword or ID keyword_or_ID: such as 'c1_', 'c5_', 12, 44 Note: assume that the 'clipr' package is installed. install.packages('clipr') Example 1>.data2excel('c1_') # all images for chapter 1 ID NAME 1 6 c1_place_holder 2 28 data_c1_c16 Example 2>.d1('c1_') # the same as the above Example 3>.d1('c3_') # for chapter 3 Example 4>.d1('gdp) # search by a keyword ID NAME 1 17 c4_GDPquarterly Example 5>.d1(17) # get the data to Excel For both Windows and Mac users: Launch Excel and paste! \";.zdata2excel_(keyword_or_ID)} //////////////////////////// " .C12EXPLAIN40<-".data2Excel_Mac //////////////////////////// .data2excel_Mac<-function(keyword_or_ID){ \"Objective : keyword or ID keyword_or_ID: such as 'c1_', 'c5_', 12, 44 Note: assume that the 'clipr' package is installed. install.packages('clipr') Example 1>.data2excelMac('c1_') # all images for chapter 1 Example 2>.d3('c1_') # the same as the above Example 3>.d3(10) # getting data to Excel \";.zdata2excel_Mac_(keyword_or_ID)} //////////////////////////// " # Yen depreciation # https://www.youtube.com/watch?v=A3VUgQ04BVY # British # https://www.youtube.com/watch?v=LG149inSpIk