-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathaccount.R
97 lines (80 loc) · 4.04 KB
/
account.R
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
library(xlsx)
library(data.table)
####################################################
month=c("Jan","Feb","Mar","Apr","May","Jun","July","Aug","Sep","Oct","Nov","Dec")
for(i in 1 : 12){
url=paste("C:/Users/Lovebonito/Desktop/d/",i,".csv",sep = "")
#url=paste("C:/Users/Lovebonito/Desktop/d/12.csv")
dt=read.csv(url)
if(i==1){
dt.all=dt
}
dt=dt[-c(length(dt[,1]),length(dt[,1])-1),]
dt.all=rbind(dt.all,dt)
s=strsplit(as.character(dt$Transaction.Date),split=" ")
m=matrix(unlist(s),ncol=2,byrow=T)
dates <- strftime(m[,1],"%Y-%m-%d",usetz = FALSE)
dt.12=data.table(cbind(dt,dates))
table(dt.12$Bank)
dt.a=dt.12[dt.12$Bank!="AMEX",]
a=dt.a[,sum(Amount),by=list(dates)]
dt.b=dt.12[dt.12$Bank == "AMEX",]
b=dt.b[,sum(Amount),by=list(dates)]
a$AMEX=b$V1[match(a$dates,b$dates)]
all=data.frame(Dates=a$dates,ENETS=a$V1,AMEX=a$AMEX)
setnames(all,c("Dates","ENETS","AMEX"))
c=data.frame(Dates=c("sum_all",sum(all$ENETS,all$AMEX,na.rm=T)),ENETS=c("sum_enets",sum(all$ENETS)),AMEX=c("sum_amex",sum(all$AMEX,na.rm=T)))
if(sum(is.na(all$AMEX))!= 0){
all[is.na(all$AMEX),]$AMEX = 0
}
f=rbind(all,c)
write.xlsx(f,"C:/Users/Lovebonito/Desktop/final.xlsx",sheetName=month[i],append=T)
}
########
#AMEX
########
for( i in 1:12){
dt=read.xlsx("C:/Users/Lovebonito/Desktop/d/final.xlsx",sheetName=month[i])
dt.AMEX=data.frame(Dates=dt$Dates,AMEX=dt$AMEX)
write.xlsx(dt.AMEX,"C:/Users/Lovebonito/Desktop/d/AMEX.xlsx",sheetName=month[i],append=T,row.names=F)
}
########
##Jan
########
Jan=(read.table("C:/Users/Lovebonito/Desktop/d/AMEX/AMEX Jan.txt",header=FALSE,sep=" "))
Jan.1=0
for(i in 1 :198){
Jan.1[i]=as.vector(Jan[1,i])
}
df=data.frame(matrix(Jan.1,ncol=6,byrow=T))
setnames(df,c("Submission.Dates","Summary","Gross.AMT","Net.AMT","Settlement.AMT","Settlement.Date"))
write.xlsx(df,"C:/Users/Lovebonito/Desktop/d/AMEX/jan.xlsx",sheetName="1",row.names=F)
df.2=read.xlsx("C:/Users/Lovebonito/Desktop/d/AMEX/2paymentstatement-Feb14.xlsx",sheetIndex=1,startRow=3,header=F, stringsAsFactors=F)
df.f=data.frame(Submission.date=df.2$X5,Settlment.date=df.2$X6,Gross.AMT=df.2$X9,Discount.AMT=df.2$X12,Net.AMT=df.2$X15,ds=df.2$X17)
df.f=df.f[!is.na(df.f$Submission.date),]
write.xlsx(df.f,"C:/Users/Lovebonito/Desktop/d/AMEX/feb.xlsx",sheetName="1",row.names=F)
df.2=read.xlsx("C:/Users/Lovebonito/Desktop/d/AMEX/12paymentstatement-Dec14.xlsx",sheetIndex=1,startRow=4,header=F, stringsAsFactors=F)
df.f=data.frame(Submission.date=df.2$X5,Settlment.date=df.2$X6,Gross.AMT=df.2$X9,Discount.AMT=df.2$X12,Net.AMT=df.2$X15,ds=df.2$X17)
df.f=df.f[!is.na(df.f$Submission.date),]
df.f$Submission.date=as.Date(as.character(df.f$Submission.date),origin = "1899-12-30")
write.xlsx(df.f,"C:/Users/Lovebonito/Desktop/d/AMEX/Dec.xlsx",sheetName="1",row.names=F)
Janx=read.xlsx("C:/Users/Lovebonito/Desktop/d/AMEX.xlsx",sheetName="Jan")
Janx.1=Janx[!is.na(Janx$AMEX),]
Janx.1=Janx.1[as.numeric(as.character(Janx.1$AMEX))!=0,]
Janx.1=Janx.1[!is.na(Janx.1$AMEX),]
Janx.2=Janx.1[substring(as.character(Janx.1$Dates),1,1)=="2",]
df$Submission.Dates=strptime(df$Submission.Dates,"%d/%m/%y")
df$Settlement.Date=strptime(df$Settlement.Date,"%d/%m/%y")
Janx.2$Dates=strptime(Janx.2$Dates,"%Y-%m-%d")
Janx.2$Dates[29] == df$Submission.Dates[4]
df.1=df[!duplicated(df[,c(5,6)]),c(5,6)]
df$Dates=Janx.2$Dates[match(df$Submission.Dates,Janx.2$Dates)]
df$AMT=Janx.2$AMEX[match(df$Submission.Dates,Janx.2$Dates)]
df$Date.S=df.1$Settlement.AMT[match(df$Submission.Dates,df.1$Settlement.Date)]
table(match(Janx.2$Dates,df$Submission.Dates))
Janx.2$Dates[Janx.2$Dates %in% df$Submission.Dates]
################################################
#read bank statement
###############################################
dir="C:/Users/Lovebonito/Desktop/wan yin finance/statement"
month=c("Jan","Feb","Mar","Apr","May","Jun","July","Aug","Sep","Oct","Nov","Dec")