Excel理财 收支管理 代码.docx
- 文档编号:17617321
- 上传时间:2023-07-27
- 格式:DOCX
- 页数:60
- 大小:172.25KB
Excel理财 收支管理 代码.docx
《Excel理财 收支管理 代码.docx》由会员分享,可在线阅读,更多相关《Excel理财 收支管理 代码.docx(60页珍藏版)》请在冰点文库上搜索。
Excel理财收支管理代码
Excel收支管理系统
程序功能:
●银行信息记录
a)存款利息计算
b)银行总资金汇总
c)银行年收益计算
●收支记录
a)收入项目记录,增加到银行账户
b)支出项目记录,选择支出账户
c)可对每条记录进行修改,并与账户关联
d)收支项目管理,可增加或删除收支项目
本程序操作灵活,界面人性化,比如删除“银行记录”金额,可将本条记录信息全部删除(需要确认);收支记录中信息输入完整,自动与银行账户信息关联;可自己添加银行并修改利率。
使用本程序可快速判别存款方式对收益的影响,比如5万存入工商银行:
1.整存整取两年,利息4400
2.整存整取一年,利息3561(两年后取)
现在银行利率也有差别,存不同银行收益相差多少也能方便了解。
界面“银行记录”
“银行记录”中复制代码如下:
PrivateSubCalendar1_Click()
ActiveCell=Calendar1
Calendar1.Visible=False
EndSub
PrivateSubWorksheet_SelectionChange(ByValTargetAsRange)
Dimlv
Dimzhuancun(1To100)
Dimlv_huo(1To1000)
Dimlv_ding1_3(1To1000)
Dimlv_ding1_6(1To1000)
Dimlv_ding1_12(1To1000)
Dimlv_ding1_24(1To1000)
Dimlv_ding1_36(1To1000)
Dimlv_ding1_60(1To1000)
Dimlv_ding2_12(1To1000)
Dimlv_ding2_36(1To1000)
Dimlv_ding2_60(1To1000)
DimrngAsRange
rn=Range("b65536").End(xlUp).Row'最大行号
cn=Range("b2").End(xlToRight).Column'最大列号
Application.ScreenUpdating=False
'数据初始化
IfSheet1.Cells(ActiveCell.Row,1)=""AndSheet1.Cells(ActiveCell.Row,3)=""AndSheet1.Cells(ActiveCell.Row,2)<>""Then
Sheet1.Cells(ActiveCell.Row,1)="中国银行"
EndIf
Fory=3Torn
IfSheet1.Cells(y,1)<>""Then
Sheet4.Select
'查找银行名称
Setrng=Sheet4.[B:
B].Find(Sheet1.Cells(y,1))
'定位银行
IfNotrngIsNothingThen
'rng.Font.ColorIndex=3'颜色暂不设置
Application.GotoReference:
=rng.Address(,,xlR1C1)
EndIf
EndIf
lv_huo(y)=Sheet4.Cells(ActiveCell.Row+3,ActiveCell.Column+1)
lv_ding1_3(y)=Sheet4.Cells(ActiveCell.Row+6,ActiveCell.Column+1)
lv_ding1_6(y)=Sheet4.Cells(ActiveCell.Row+7,ActiveCell.Column+1)
lv_ding1_12(y)=Sheet4.Cells(ActiveCell.Row+8,ActiveCell.Column+1)
lv_ding1_24(y)=Sheet4.Cells(ActiveCell.Row+9,ActiveCell.Column+1)
lv_ding1_36(y)=Sheet4.Cells(ActiveCell.Row+10,ActiveCell.Column+1)
lv_ding1_60(y)=Sheet4.Cells(ActiveCell.Row+11,ActiveCell.Column+1)
lv_ding2_12(y)=Sheet4.Cells(ActiveCell.Row+13,ActiveCell.Column+1)
lv_ding2_36(y)=Sheet4.Cells(ActiveCell.Row+14,ActiveCell.Column+1)
lv_ding2_60(y)=Sheet4.Cells(ActiveCell.Row+15,ActiveCell.Column+1)
'返回sheet“银行项目”
Sheet1.Select
Next
'--------------
'格式初始化
WithRange(Sheet1.Cells(3,1),Sheet1.Cells(rn+30,cn)).Interior
.Pattern=xlNone
.TintAndShade=0
.PatternTintAndShade=0
EndWith
'---------------
'取消列表
WithSheet1.Range("A:
A").Validation
.Delete
.AddType:
=xlValidateInputOnly,AlertStyle:
=xlValidAlertStop,Operator_
:
=xlBetween
.IgnoreBlank=True
.InCellDropdown=True
.InputTitle=""
.ErrorTitle=""
.InputMessage=""
.ErrorMessage=""
.IMEMode=xlIMEModeNoControl
.ShowInput=True
.ShowError=True
EndWith
WithSheet1.Range("C:
C").Validation
.Delete
.AddType:
=xlValidateInputOnly,AlertStyle:
=xlValidAlertStop,Operator_
:
=xlBetween
.IgnoreBlank=True
.InCellDropdown=True
.InputTitle=""
.ErrorTitle=""
.InputMessage=""
.ErrorMessage=""
.IMEMode=xlIMEModeNoControl
.ShowInput=True
.ShowError=True
EndWith
'银行列表更新
rn4=Sheet4.Range("e65536").End(xlUp).Row
f="=基本信息!
E5:
E"&rn4&""
WithRange("A3").Validation
.Delete
.AddType:
=xlValidateList,AlertStyle:
=xlValidAlertStop,Operator:
=_
xlBetween,Formula1:
=f
.IgnoreBlank=True
.InCellDropdown=True
.InputTitle=""
.ErrorTitle=""
.InputMessage=""
.ErrorMessage=""
.IMEMode=xlIMEModeNoControl
.ShowInput=True
.ShowError=True
EndWith
'项目列表
WithRange("C3").Validation
.Delete
.AddType:
=xlValidateList,AlertStyle:
=xlValidAlertStop,Operator:
=_
xlBetween,Formula1:
="活期,整存整取,整存零取,零存整取,存本取息,定活两便"
.IgnoreBlank=True
.InCellDropdown=True
.InputTitle=""
.ErrorTitle=""
.InputMessage=""
.ErrorMessage=""
.IMEMode=xlIMEModeNoControl
.ShowInput=True
.ShowError=True
EndWith
ForZ=3Torn
'银行列表更新
rn4=Sheet4.Range("e65536").End(xlUp).Row
f="=基本信息!
E5:
E"&rn4&""
WithRange("A"&Z+1).Validation
.Delete
.AddType:
=xlValidateList,AlertStyle:
=xlValidAlertStop,Operator:
=_
xlBetween,Formula1:
=f
.IgnoreBlank=True
.InCellDropdown=True
.InputTitle=""
.ErrorTitle=""
.InputMessage=""
.ErrorMessage=""
.IMEMode=xlIMEModeNoControl
.ShowInput=True
.ShowError=True
EndWith
'项目列表
WithRange("C"&Z+1).Validation
.Delete
.AddType:
=xlValidateList,AlertStyle:
=xlValidAlertStop,Operator:
=_
xlBetween,Formula1:
="活期,整存整取,整存零取,零存整取,存本取息,定活两便"
.IgnoreBlank=True
.InCellDropdown=True
.InputTitle=""
.ErrorTitle=""
.InputMessage=""
.ErrorMessage=""
.IMEMode=xlIMEModeNoControl
.ShowInput=True
.ShowError=True
EndWith
'-----------------------
'年利率
IfSheet1.Cells(Z,3)="Huo"OrSheet1.Cells(Z,3)="活期"Then
lv=lv_huo(Z)
Sheet1.Cells(Z,6)=lv
EndIf
IfSheet1.Cells(Z,3)="ZZ"OrSheet1.Cells(Z,3)="整存整取"Then
IfSheet1.Cells(Z,5)<6Then
lv=lv_ding1_3(Z)'3个月
EndIf
IfSheet1.Cells(Z,5)>=6AndSheet1.Cells(Z,5)<12Then
lv=lv_ding1_6(Z)'半年
EndIf
IfSheet1.Cells(Z,5)>=12AndSheet1.Cells(Z,5)<24Then
lv=lv_ding1_12(Z)'1年
EndIf
IfSheet1.Cells(Z,5)>=24AndSheet1.Cells(Z,5)<36Then
lv=lv_ding1_24(Z)'2年
EndIf
IfSheet1.Cells(Z,5)>=36AndSheet1.Cells(Z,5)<60Then
lv=lv_ding1_36(Z)'3年
EndIf
IfSheet1.Cells(Z,5)>=60Then
lv=lv_ding1_60(Z)'5年
EndIf
Sheet1.Cells(Z,6)=lv
EndIf
IfSheet1.Cells(Z,3)="ZLLZBX"OrSheet1.Cells(Z,3)="零存整取"OrSheet1.Cells(Z,3)="整存零取"OrSheet1.Cells(Z,3)="存本取息"Then
IfSheet1.Cells(Z,5)>=12AndSheet1.Cells(Z,5)<36Then
lv=lv_ding2_12(Z)'1年
EndIf
IfSheet1.Cells(Z,5)>=36AndSheet1.Cells(Z,5)<60Then
lv=lv_ding2_36(Z)'3年
EndIf
IfSheet1.Cells(Z,5)>=60Then
lv=lv_ding2_60(Z)'5年
EndIf
Sheet1.Cells(Z,6)=lv
EndIf
IfSheet1.Cells(Z,3)="定活两便"Then
IfSheet1.Cells(Z,4)=""AndSheet1.Cells(Z,5)=""Then
dh=MsgBox("未区分各家银行计算方法,结果不一定准确,按利率60%计算?
",vbYesNo,"提示")
Ifdh=vbYesThen
IfSheet1.Cells(Z,5)<6Then
lv=lv_ding1_3(Z)'3个月
EndIf
IfSheet1.Cells(Z,5)>=6AndSheet1.Cells(Z,5)<12Then
lv=lv_ding1_6(Z)'半年
EndIf
IfSheet1.Cells(Z,5)>=12AndSheet1.Cells(Z,5)<24Then
lv=lv_ding1_12(Z)'1年
EndIf
IfSheet1.Cells(Z,5)>=24AndSheet1.Cells(Z,5)<36Then
lv=lv_ding1_24(Z)'2年
EndIf
IfSheet1.Cells(Z,5)>=36AndSheet1.Cells(Z,5)<60Then
lv=lv_ding1_36(Z)'3年
EndIf
IfSheet1.Cells(Z,5)>=60Then
lv=lv_ding1_60(Z)'5年
EndIf
lv=lv*0.6
Else
Sheet1.Cells(Z,3)=""
EndIf
Else
IfSheet1.Cells(Z,5)<6Then
lv=lv_ding1_3(Z)'3个月
EndIf
IfSheet1.Cells(Z,5)>=6AndSheet1.Cells(Z,5)<12Then
lv=lv_ding1_6(Z)'半年
EndIf
IfSheet1.Cells(Z,5)>=12AndSheet1.Cells(Z,5)<24Then
lv=lv_ding1_12(Z)'1年
EndIf
IfSheet1.Cells(Z,5)>=24AndSheet1.Cells(Z,5)<36Then
lv=lv_ding1_24(Z)'2年
EndIf
IfSheet1.Cells(Z,5)>=36AndSheet1.Cells(Z,5)<60Then
lv=lv_ding1_36(Z)'3年
EndIf
IfSheet1.Cells(Z,5)>=60Then
lv=lv_ding1_60(Z)'5年
EndIf
lv=lv*0.6
EndIf
Sheet1.Cells(Z,6)=lv
EndIf
'-------------------------------------------
OnErrorResumeNext
IfTarget.Column=4AndTarget.Value<>"存入日期"AndTarget.Row>2AndTarget.Row<=rnThen
Calendar1.Visible=True'日历控件
Calendar1.Left=Cells(Target.Row,4).Left
Calendar1.Top=Cells(Target.Row+1,4).Top
WithCalendar1'当前日期
.Year=Year(Now)
.Month=Month(Now)
.Day=Day(Now)
EndWith
ElseIfTarget.Column=1AndTarget.Row=1Then
Calendar1.Visible=True'日历控件
Calendar1.Left=Cells(Target.Row,1).Left
Calendar1.Top=Cells(Target.Row+1,1).Top
WithCalendar1'当前日期
.Year=Year(Now)
.Month=Month(Now)
.Day=Day(Now)
EndWith
Else
Calendar1.Visible=False
EndIf
OnErrorResumeNext
nian=Int(Sheet1.Cells(Z,5)/12)'年数
yue=Sheet1.Cells(Z,5)-12*nian'月数
lixishui=0'利息税率
daoqi=Sheet1.Cells(Z,2)+Sheet1.Cells(Z,2)*(lv/100)*(Sheet1.Cells(Z,5)/12)*(1-lixishui)'到期总数
'当前日期与存入日期相差月数
IfYear(Sheet1.Cells(1,1))>=Year(Sheet1.Cells(Z,4))AndMonth(Sheet1.Cells(1,1))>=Month(Sheet1.Cells(Z,4))AndDay(Sheet1.Cells(1,1))>=Day(Sheet1.Cells(Z,4))Then
m=(Year(Sheet1.Cells(1,1))-Year(Sheet1.Cells(Z,4)))*12+Month(Sheet1.Cells(1,1))-Month(Sheet1.Cells(Z,4))
EndIf
IfYear(Sheet1.Cells(1,1))>=Year(Sheet1.Cells(Z,4))AndMonth(Sheet1.Cells(1,1))>=Month(Sheet1.Cells(Z,4))AndDay(Sheet1.Cells(1,1)) m=(Year(Sheet1.Cells(1,1))-Year(Sheet1.Cells(Z,4)))*12+Month(Sheet1.Cells(1,1))-Month(Sheet1.Cells(Z,4))-1 EndIf IfYear(Sheet1.Cells(1,1))>=Year(Sheet1.Cells(Z,4))AndMonth(Sheet1.Cells(1,1)) m=(Year(Sheet1.Cells(1,1))-Year(Sheet1.Cells(Z,4)))*12-Month(Sheet1.Cells(1,1))+Month(Sheet1.Cells(Z,4)) EndIf IfYear(Sheet1.Cells(1,1))>=Year(Sheet1.Cells(Z,4))AndMonth(Sheet1.Cells(1,1)) m=(Year(Sheet1.Cells(1,1))-Year(Sheet1.Cells(Z,4)))*12-Month(Sheet1.Cells(1,1))+Month(Sheet1.Cells(Z,4))-1 EndIf IfYear(Sheet1.Cells(1,1)) m=0 EndIf IfYear(Sheet1.Cells(1,1))=Year(Sheet1.Cells(Z,4))AndMonth(Sheet1.Cells(1,1)) m=0 EndIf IfYear(Sheet1.Cells(1,1))=Year(Sheet1.Cells(Z,4))AndMonth(Sheet1.Cells(1,1))=Month(Sheet1.Cells(Z,4))AndDay(Sheet1.Cells(1,1)) m=0 EndIf '---
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel理财 收支管理 代码 Excel 理财 收支 管理