Excel VBA编程与应用 课程名称：SEC214 Excel VBA编程与应用Excel VBA Programming and Application 课程性质：本学完选修实验课程 学分课时：2学分，32课时，其中实验课时16课时 主讲救师：冯建芬副牧授 所属院系：金融学院金融工程系 电话：64495048E-mail:danxin97@163.com 敦学对象：金融学院大学二年级学生 授课方式：讲授、实验（上机）、案例相结合 考核方式：小组作业(20%)小组编程任务 课堂实验(10%)个人绵程任务，要求当堂完成，当堂提交 期中考试：上机考试(30%)在规定时间内完成编程任务，并提交 期末考试：论文考试(40%)分小组完成编程和论文撰写 其中小组作业和课堂实验届于平时成绩，占30%，期中考试占30%，期末考试占40%， 学术诚信：本课程对于学生的学术诚信的要求遵从《对外经济贸易大学学生违纪处分条例》、《对 外经济贸易大学学生学习违纪处分实施细则》、 《对外经济贸易大学考场纪律》的规定。 救学方式：课堂讲授占比30%，上机实验占70%，教学中强调动手能力，采用实验教学、实践救学和研讨救教学相结合的方式。教学全程在金融实验室完成，结合excel软件、Ocale实 验教学平台、金融数据库和PPt进行教学。 出勤要求：遵从《对外经济贸易大学本科生课堂学习规范》，要求学生关闭一切电子设备：不能 无故缺席上课；上课专心听讲，积极参与课堂讨论：课后认真复习课堂上讲授内容，独立完成教师布置的任务：并预习新课。学生缺勤不得多于总课时的四分之一，教师 可以根据考勤情况决定学生是否可以参加考试、是否扣分， 一、课程简介： Exce VBA:是金融机构在财务数据管理、金融数据处理中使用最广泛、成本最小的数据处理、展现与分析软件，大型金融数据库如彭博、万德、路透等都是通过ecl与其他程序 语言接口进行数据供给的，大部分金融实验软件也是通过exce VBA功能实现金融模型的量化。因此深入了解exCl的VBA操作可以提高学生的动手能力、就业能力和获取更多帮助资 源的能力，本课程作为金融学院专业选修课程，系统地讲解Excel VBA的基本语法及在数据处理中的应用，具体内容包括：exCl表格的深入操作，eXC©数据分析工具应用，宏与 VBA程序设计，窗体、莱单及数管理程序，VBA与外部数获取，VBA与其他软件接口管理、excel VBA的金融应用案例解析等。 二、教学目标 通过本课程的学习，希望学生能够熟熱练运用excel VBA进行更高效更快捷的数据处理和管理，能够轻松解读可获得的excel VBA程序，并结合自我需求进行程序调制，有效提高 其动手能和协助教师进行学术研究的能力，提升专业的实验课程或专业课程的实验环节的实验效果，为进行大三的实习，大四就业打好基础。 三、课程学习资料 1教材： 《Exc与数处理》（第5版），杜茂康编苦，电子工业出版社，2014 2.参考资料 《Exc2010VBA煸程与实践》，罗列君章兰新黄朝阳编著，电子工业出版社，2010 《基于exc和VBA的高级金键模》，朱世武，何侧波译，中回人民大学出版社，2006 《excel VBA快速上手之宝典》，电子书，2006 Mastering.Financial.Mathematics.in.Microsoft.Excel Excel+SQL server数据库管理技术详解 四 学习效果及达成途径 学习效果： 通过本课程的学习，希望达成的学习效果如下： 1能的够灵活运用excelp内置函数、exce公式、数组公式，进行单元格数据处理操作 2.掌握exc的数据处理工具的应用，包括模拟运算表、单变量求解、数据分析工具和规划求解工具， 3.掌握VBA语言的基本编程语法，能够解读VBA程序并进行改进.。 4,掌挥通过宏对单元格、单元格区域以及工作表甚至多个工作簿的操作 5能够通过修改图表操作的宏，满足自己通过宏进行图表操作的需求 6.掌握通过VBA调用excelp内置函数、加载宏工具的方法 7能够运用VBA语言定义函数和左扩展excel的数据处理和展现功能。 8掌握如何通过VBA查询和选金融数据库的数据。 9掌握如何通过VBA调用5QL查询语言进行数据查询. 10.掌如何结合exC设计工具创调用宏的工具按钮、菜单等，以此完成应用软件开发的初步换作， 2达成学习效果的途径 善于动手，课前完成例题程序的上机任务；上课跟着老师思路走，积极参与课堂讨论；充分利用实验教学平台的讨论区和教师指定的答疑时间及时答疑解感：按时完成课堂实践任 务和课下作业，认真准备期中上机考试，认真完成期末论文。 五、救学进度计划表 本课程教学周为16周，具体安排如下 周 次 内容提要 参考资料 作业与 考试 笙一者 教材第 速下/ exe八与 1.5,9,11章 组作业 函西数应用 上机买份：公 课堂实 式应用与数学 践测验 模型转换 草一： 教材第8章 课下作 excel的数据 业 分标只应用 上机实验： 课堂迹 4 excel数据分 践测验 析工具应用 第三 敏第1》 课堂实 章：VBA编 章；excel 践测 程简代 VBA快速上 验：课 3.1宏与 手之宝典 下作业 录制宏 3.2 VBA程序设 计基础 3.3 课下作 VBA的程序 结构 6 34 WBA程京的 两种定义形 式：子程序与 正数 上机实 理常动 验： 宏与西数 践测验
周 次 内容提要 参考资料 作业与 考式 第四章自定 救材第12 义宏的起作 4.1宏对单元 《Excel 格的引用， 编 2010 VBA 面和整故 编程与实 4.2宏对单元 格区域的引 用、编和格 4.3宏对多个 工作表的操作 4.4宏对客个 课堂实 工作薄的操作 践测验 4.5正则表达 式在查询中的 使用 4.6自动宏 课下作 10 47宏对图表 业 的操作 11 上机实验：编 课堂实 制宏练习 践测验 范五高自电以 课下作 函数的操作 业 5.1自定义函 数举例 12 5.2为自定义 函数添加帮助 信得 5.3金融数据 库的数据查询 与下提 13 上机实验：自 课堂实 定义函数练习 践测验 第六 分小组 章excel查 布置期 询与外部数 末论文 据交换 任务： 6.1 Excel与 课下作 数据库钢述 6.2 14 Microsoft Query与外 部数据库访 6.3 Excel与 其他文件类型 的相互转换 6.4s0L查 询工作表 上机空粉： 生堂边 15 excel数据查 践测验 询与数据转换 习 第七章excel 小组作 16 VBA应用宗台 业研讨 实践 17- 完成期末论文 18 六、教学内容 第一章excel公式与函数应用 【教学目的和要求】： 1.使学生了解exce公式的书写规则、操作算符、操作对像： 2.能够使用xc公式对单元格或者单元格区域进行绝对引用和相对引用、会进行数组公式计算： 3.能够结合excelf的各种内置函数灵活使用excelf的公式，进行单元格数据处理操作； 4.能够为单元格或者单元格区域命名，并在公式中灵活使用单元格名称进行数据处理操作， 5.能韵够通过解读已有的exc单元格数据处理文件井还原数据处理过程： 【主要内容】 1.1exce公式应用 公式的书写规则 公式的操作算符与操作对象 公式与单元格引用 数组公式与名字应用 公式的复制与自动重算 函数应用 函数简介 常用工作表函数分类 工作表函数应用举例 课堂练习：公式应用与数学模型转换 教学总时数：4，其中实验课时2课时 参考资料： 1.《Exc与数据处理》（第5版），杜茂康等编著，电子工业出版社，2014，第一章.第五章，第九意、第十一章： 2.Mastering Financial Mathematics in Microsoft Excel,电子书 3.《基于exc的高级金速模》，杰克逊，斯汤顿著，朱世武，何剑波译，中国人民大学出版社，2006，第二章 作业与练习： 课堂实验(2课时) 1现有美国住宅抵押贷款利率历史数据但日期不是日期格式的数值型数据请将日期数据修正为数值型数据。然后画出关国30年贷款利率和1年期贷款利率的时序图，横轴为日期，纵轴为利 率
提示 步骤1.首先将数据通过字符串函数运算改为日期格式的字符串表示如”10-2-1992”先改为°1992-10-2”，"12/11/2009”改为”2009/12/11" 步骤2.通过year(),month(),day0函数提取字符串中的年.月.日，然后通过date()形成日期. 2由于将股票代码存储成数值型数据，导致沪市股票代码前面的0丢失， 1)请结合函数，利用公式补足前面的0，将股票代码改为正确的显示型式， 提示所有股票代码都是6位数字表示的，所以不足6位的发在前面添"0 01改为000001. 2)有时候为了方便，需要将上述股票代码转换为数值格式的，请结合函数利用公式将其转换为数值格式，如000001，转换为1， 3利用随机数函数进行随机分组， 给班里同学每三个人分成一组，剩余不足三人的最后形成一个组。 提示： 1).首先在班级名册上利用rand0产生随机数 2),对表格按照随机数进行升序#列 3)产生一列作为每个同学的序号 4)按照序号进行每三个人一组的分组，给出每个人的组号 4利用countif0函数统计在“选择银行的原因”列表中，每一项(A,B,C,D,E,F,G,H,l月，文字列为“其他原因“)选择的个数，如选择“ABCD°的，则在“A”的统计中应该计算，在B“的统计 中也应该十组 5.概率统计函数应用：均值、方差、标准差、概率分布值、随机数 给出美国抵押贷款利率数据。 1)利用指定首行为名称，为利率数据单元格区域定义名称 2)利用该名称和统计团数每一列贷款利率的均值、方差、标准差、中位数、众位数 3)以给出的30年期贷就利率为样本，计算样本5%的分位数。 4)利用rand0函数，无重复地对30年期的货款利率进行抽样，随机抽出100个样本 课下小组作业 以组为单位，每组三人，共计18个组， 组选泽三个作业的其中一个进行excel文件的公式解读，形成小组报告和视频报告. 要求 1通过查找文献给出该模型的介绍。不需要给出模型的推导或者深入的理解原理。 2对应模型个绍，给出每个作业中横#实现的完整步骤，并给出在实现过程中每一步对应的exc公式。 不限字数。 第二章excel的数据分析工具应用 【教学目的和要球】 1,了解如何运用excl的模拟运算表，单变量求解功能进行重复运算和变量求解 2.了解如何使用excel的加载宏功能加载数据分析工具和规划求解工具； 3.能够使用excl提供的上述工具进行数据计算，如模拟运算、非线性方程求解、回归分析、产生一定概率分布的随机数等 【主要内容】 2.1 excel的模拟运算表应用 建立单变量模拟运算表 建立双变量模拟运算表 应用练习： 2.2.excel的单变量求解与规划求解应用 单变量求解与规划求解的使用方法 单变量求解与规划求解的应用举例 2.3.excel的数据分析工具应用 回归工具应用举例 随机数发生器与抽样工具应用举例 统计分析工具应用 数据分析工具应用练习 救学总时数：4课时，其中实验课时2课时 1.《Excl与数据处埋》（第5版），杜茂康等绵著，电子工业出版社，2014，第八章 2.《基于xcl的高级金融建模》，杰克逊，斯汤顿著，朱世武，何剑波译，中国人民大学出版社，2006，第二章，第六章 作业与练习： 课堂实验(2课时)： 1.分别利用单变量求解和规划求解估计期权价值的隐含波动率 1)已知某产品的价值满足如下表达式： 2 其中 N(,表示标准正态分布的累积分布函数： 这里函数的取值是已知 的 即产品的价值另外参数S0,XT,r都是已知的，参见excl表格第二讲习.Msx,练习2.1 试通过单变量求解计算σ的取值。 注：「任0.1之▣是合的.否不合理) 2,利用规求解估计非线性方程组的释： 已知公司权益价值满足如下表达式： (1) 同时，权价值的波动率满足如下表达式 2(2) 其他参数取值已知的情况下，通过规求解，估计公式(1)，(2)联立方程组中的V0和，具体数值和求解变量初值见第二讲练习.x5x,练习22 约束条件 E0,v0>D,0<oV<1 3.求解练习2.3中，pice与Z1-Z10之间的回归系数，其中price为因变量，Z1-Z10为自变量，设常数项为0，分别使用数据分析工具和inesti函数实现. 4.现有中信证券的历史交易记录，利用数据分析工具中的抽样工具随机抽出1000个复权收盘价： 5.设每一月的殺票价格满足如下递准关系 其中deltat=1/12 mu=0.05,0=0.30,S0=10,为标准正态分布的随机变量，在递准过程中任何两步的e是相互独立的.试通过随机数发生器，产生股票价格1年的价格路径100条，每条路径上有 12个月的股票价格S0,S1,S12并绘制出这100条路径的折线图（在同一铜图中） 第三章：VBA编程简介 【教学目的和要求】
1能够通过录制宏功能了解VBA程序的基本构造： 2.了解VBA编程的原理和特点 3.掌握VBA的基本编程语法 4.能够解读VBA的程序，并在此基础上进行修改 【主要内容】 宏与录制宏 3.1.1exce中宏的概念 录制宏 编辑、查看宏代码 宏的安全性配置 宏的保存与执行 3.2VBA程序设计基础 3.2.1VBA的变量定义与数据类型 运算符 数组定义与引用 对象、属性和方法的概念 如何查找和应用对象、属性和方法 3.3VBA的程序结构 顺序结构 选择结构一条件语句 多分支结构一多分支语句 循环结构—循环语句 3.4VBA程序的两种定义形式：子程序与函数 子程序（即宏）的定义与调用方式 函数的定义与调用方式 的程序调试与注释语句的使用 程序加密 敦学总时数：6，其中实验课时2课时 参考资料： 1.《ExC与数据处理》（第5版），杜茂康等综著，电子工业出版社，2014，第12章 2.《基于excl的高级金融建模》，杰克逊，斯汤颠著，朱世武，何剑波译，中国人民大学出版社，2006，第三章，第四章 3.《ExC2010VBA编程与实践》，罗刚君章兰新黄朝阳编著，电子工业出版社，2010 4.《excel VBA快速上手之宝典》，电子书，2006 2课时)： 练习要求：对于作业中的关键公式和语句在程序中通过注释说明作用，在程序开头通过注释说明此宏(sub)或者函数(function)的作用 1.1)编写一个宏，通过消息框msgbox(),显示工作表“练习1"中B5单元格中的值 2)将1到5的平方根写入工作表练习1的A1A5中，使用for一next循环来实现。 2编写宏，产生数组练习： 1)编写两 个宏 一个宏为thefifths(n),产生包含n个元素的整型数据数组A,数组元素分别赋值为1,2，，n的5倍数值，如thefifths(4)产生的数组元素取值 为：5,10,15,20，在该宏中通过调用已经编写好的arrayprint程序将结果输出到“"立即窗口“，调用方式为call arrayprint(A) 另一个宏"练习1_1“用于调用宏thefifths(n. 2)编写一个宏nultiplytwo(n),产生包含n个元素的数组A,A中第一个元素取值为1，第2个元素取值为2，如果n>2,从第3个元素开始，每个元素的取值为其前面两个元 素的乘积 如nultiplytwo(7)的结果为：1,2,3,4,8,32,256.同样在该宏中通过调用已经编写好的arrayprint程序将结果输出到"立即窗口"，调用方式为call arrayprint(A). 同时编写另外一个宏“练习12“用于调用宏multiplytwo(n) 3)编写一个宏colnum(n,m),产生包含n*m个元素的二维数组A,A中每个元素取值为其对应列下标值的2倍。 例如：colnum(3,5)产生的矩阵为： 246810 246810 246810 同样在该宏中通过调用已经编写好的arrayprint程序将结果输出到"“立即窗口“，调用方式为call arrayprint(A), 同时编写另外 -个宏"练习13“用于调用宏colnum(n) B.已有某单位的职工工资表内容表格” 练习31“编写 个宏，计算表中的工资总额、税率，所得税、实发工资，其中，工资总领的计算方法如下： 工资总额=加班工资+房层补贴+其他津贴+生计津贴+理智津贴-餐券 税率的计算方法如下： 税率 对应工资总额 0.05 800<=工资总额 <1000 0.07 1000<=工资总款 c1500 0.1 1500<=工资总额 c2000 0.15 工资总额>=2000 所得税和实发工资的计算方法如下： 所得税=工资总额·税率 实发工资-工资总颤-餐券 工资表中的职工人数用while..wend循环或者Do.oop循环 4.设计一个excl表格，用于生成指定平均收益和波动率的股价走势. 要求：平均收益、波动率、初始股价、模拟时间（年）可以在单元格中更改。 编写一个宏”股价数据”，从单元格中读取平均收益。波动率、初始股价、模拟时间（年）的数据，结合vba的函数rnd0和excelf的内置函数normsinv()生成标准正态分布 随机数，并利用以下递推公式，模拟一支股票规定时间内的价格路径100条。每条路径为一列数据，在第一行写明标题表明该列是第几条路径. 参考初值：S0=20,mu=0.05,sigma=0.32,T=1(年)，从单元格获得数据可参考”欧美式期权的二叉树" 5.编写两个函数function)BS_cal和BS_put,分别用于对于给定的参数S,rX,o,T计算下面两个函数的取值
设定参数的初值如下5=50,0=0.2，r=0.1,T=1,X=60 1)在表练习5中要求的单元格中分别调用BS call,,BS put,计算函数取值 2)在表练习5-1中，将对应的参数值更改列中的取值，其他数值和初值保持不变，计算BS call,BS put的取值 提示：标准正态分布的累积分布函数可以在VBA中调用excel内置函数normsdisto. 自定义宏的操作 【教学目的和要求】 1掌握通过宏对单元格、单元格区域以及工作表甚至多个工作簿的操作 2能够通过修改图表操作的宏，解决自己通过宏进行图表操作的需求 3.了解如何通过宏引用excel内置函数 4.了解如何通过宏引用其他数据分析工具如规划求解、回归等 【主要内容】 宏对单元格的引用、编辑和修改 宏对单元格区域的引用、编辑和修改 宏对多个工作表的操作 宏对多个工作簿的操作 自动宏 宏对图表的操作 编制宏练习 敦学总时数：6，其中实验课时2课时 参考资料： 1.《Exce与数据处理》（第5版），杜茂康等编著，电子工业出版社，2014，第12章 2.《基于e×c的高级金融键模》，杰克逊，斯汤顿著，朱世试，何剑波译，中国人民大学出版社，2006，第三章，第四章 3.《Exc2010VBA编程与实践》，罗刚君章兰新黄朝阳编著，电子工业出版社，2010 4.《excel VBA快速上手之宝典》，电子书，2006 作业与练习： 课堂实验(2课时)： 1完成凝难24中的要求：将成绩在85分以上的学生和不及格的学生分别以不同的颜色表示，在新的表中】 2在xcl的单元格中，以51作为间隔，使用RGB函数设置不同颜色的单元格背景，依次查看RGB名种取值下的颜色，每个单元格的取值为RGB函数三个参数的取值，如：如果 单元格A1中使用的背景颠色为RGB(255,0,0),则A1的取值为”RGB(255.0,0)” 注：以51为间隔目的是为了降低空间，如red参数的取值为0,51,102，，255；绿色和黄色也，是类似。这样共需要5^3个单元格显示不同的颜色 3.更改分离男女教师xsm背后的宏文件 1)不使 auto_open,改为自动漫索教师档案中已经使用的单元格的方式(usedrange),确定检索区域，将男女教师信息和讲师信息分别放在不同的工作表中。 2)在关闭工作簿时，同时细删除男教师、女教师、讲师三个工作表 4解读DG201 applications..x水中模块module中的自动宏的作用 5.为欧美式期权二叉树定价.sm文件的VBA程序加注释，解读数据处理过程。 6将例4.29中生成的工作簿合并成一个工作薄，合并工作簿中每个表的名字为原工作簿的名字，合并后的工作薄的名字为"合并工作薄“ 小组作业 1通过更改”获取股票历史数据水s”中的代码，实现多个股票数据的查询。要求： 针对在选定的单元格区域中输入的股票代码，查询每个股票代码2000年1月1日以来的历史交易数据，并将每个股票的数据放在单独的工作表中，工作表的名字为股票的代码 如00001的数起存储的工作表名字为“stk000001 例：查询如下股票代码的日期“，“开盘价“，“最高价“，“成交价“，“最低价“，“成交量” 000001 000002 000004 000005 000006 000007 000008 000009 600459 600460 600461 600462600463 600466 600467 3尝试将问题1中的工作薄拆分成多个工作簿，每个工作簿中存储一个股票的数据，工作薄的名字为相应工作表的名字，存放在同 一文件夹中。 4.a)对每只股票的收盘价i计算lh(p(t)ln(p(t-1)》,其中pt表示当前单元格中的收盘价，pt-1表示前一单元格中的收盘价，所有股票收益率的计算结果放在一个工作簿 stock return中，每个股票是一个工作表 包含两列数据 列是日期 列是收益 b)将stock_return中所有股票代码相同日期的收益率汇总在同一工作表中，每个股票的收益率起一个名字，如000001的收益率名字为return_000001,在该工作表中包含日期 以及每个股票的收益率信息，每个变量为一列，通过VBA宏实现。（注意收益率日期要匹配） 5实现各种排序算法，并检验各种算法的速度：冒泡排序、选择排序、插入排序、快速排序、希尔排序。给定一列数，使用各种算法对其进行排列，在表格中为各种算法增加 运行安出 6.接课件中的例4.30，查询“4.30文件夹遍历"中所有的excl文件（包括文件夹中的exc文件）将所有文件合并在同一工作薄中，每个文件的A1单元格的注释为该文件的文 件路径，合并后的工作簿名字为“子文件夹端历xm 第五章自定义函数的操作 【教学目的和要求】 1掌握如何定义和调用局部函数或者全局函数 2掌握如何为函数添加帮助信总； 3.能够在excel中灵活使用excel内置函数和用户自定义函数 4通过函数操作能的够查询金数据库中的数据： 【主要内容】 自定义函数举例 为自定义函数添动加帮助信息 5.3金融数据库的数据查询与下裁 5.2.1与Bloomberg的连接 5,22万德数据库的查询函数应用 5.2.3ROV期权估值软件的函数应用 5,4自定义函数练习 教学时数：4，其中实验课时2课时 参考资料： 《Exc与数据处理》（第5版），杜茂康等综苦，电子工业出版社，2014，第12章 2.《基于xcl的高级金键横》，杰克逊，斯汤顾著，朱世武，何剑波译，中国人民大学出版社，2006，第四前 3.《ExcI2010VBA编程与实践》，罗列君章兰新黄朝阳编著，电子工业出版社，2010 4.《excel VBA快速上手之宝典》，电子书，2006 作业与练习： 课堂实验(2课时)： 1信写 个函款，用于产生泊松分布的随机敌。要求不能使用exc的内置函数，但可以使用VBA的函数rndO。 2将欧美式二叉树模型×s的程序改写成涵数调用形式，删除二叉树的展现部分，只返回欧式期权和美式期权估值的结果，并为函数添加棉助信息，包括参数的帮助信息 函数帮助信息为：option CRR(S0,X,r.sigma,T,N,g)此函数使用CRR二叉树模型计算欧式期权和美式期权的价值，包括不知红利和支付连续红利的情况， 参数帮助信息为：S0为标的资产价格：，X为执行价格；，r为无风险利南，1>>0；sigma为波动率，1>sigma>0;T为到期期限，以年为单位：N为二叉树的步数正整数：q为标的 资产的红利率，0<q<1: 3,非线性方程求辉问题，编写计算隐含波动率的VBA函数。设满足如下方程：