Actuarial Computing Excel Access VBA Repo

精算-计算-Excel-Access-VBA-Repo

精算计算（MAT 253，ISU）

使用VLOOKUPS

此代码实验室重点介绍使用 VLOOKUPS 填写下面列出的索赔数量表格，并引用数据选项卡上的第一个表格。它涉及利用行中的值来完成 VLOOKUP 函数的第三个参数。它还涉及正确使用绝对和相对单元格引用，以便可以在整个黄色区域复制相同的函数。

使用 HLOOKUP

此代码实验室还重点介绍如何使用 HLOOKUP 使用“数据 1”选项卡的第二个表中的数据填写表。对于 HLOOKUP 的第三个参数，我们使用 MATCH 函数以及适当的匹配键和对带有可用年份列表的向量的数组引用

损失和保费计算

该代码涉及遵循以下说明：

• 损失金额和纯保费列应采用美元格式并保留两位小数。
• 声明频率列应采用百分比格式，保留 2 位小数。
• 曝光单位的格式应包含逗号且无小数位。
• 如果增长率小于 0%，则为 G 列创建条件格式。
• 满足此条件格式的单元格的字体应为粗体，并且图案应填充为红色。
• 如果增长率超过 5%，则添加第二个条件格式。
• 满足此条件的单元格的字体应为粗体，并且其图案应填充为绿色。
• 对问题 2 选项卡上的数据创建数据过滤器。
• 您应该选择 STAT 列为“MS”且 COV 为“COLL”的行。
• 当您保存文件以便返回给我时，请保持过滤器打开。

图表

在此代码实验室中，我们创建图表，在 y 轴上显示实际索赔频率和实际索赔严重性。
由于每个系列的比例差异很大，因此我们使用两个不同的轴来显示不同的系列。
x 轴显示周期#（A 列）。每个系列显示为点，并带有连接线。
每个系列都根据需要标记为频率或严重性。

使用 IF 语句

使用 IF 语句，我们计算“问题 1”选项卡上列表中每个人的精算现值。 - APV 公式 = 面值 * Axe - Axe 因性别和吸烟者状况而异，可以在每种情况的 4 个选项卡上找到。为了检查答案，第一个策略的结果应为 APV = 1,1238.0 在“问题 1”选项卡上，A 列包含一个由 4 个不同字段串联而成的文本字符串：Policy_Num、Effective_Date、Expiration_Date、Premium。使用逗号 (,) 作为分隔符将它们分成 4 列。您可以使用 Excel 中的任何工具或函数来完成此操作。

数据透视表

我们根据“集合”选项卡上的数据（范围 A1:D2771）在名为“问题 1”的新工作表中设置了一个数据透视表。将“收款数量”放入行标签中，并创建 4 列： 1. 保费总和 2. 损失总和 3. 损失率 = 损失 / 保费 4. 保单数量，显示为列的百分比。

回归

在“回归”选项卡上，使用简单线性回归 (y=a+bx) 技术根据一个人的身高来预测其体重。您可以使用 Excel 中可用的任何方法来获取参数估计值。

创建查询

• 在 access 中创建一个查询，返回 IL、IN、OH 和 MI 州的所有保单编号的列表。将此查询保存为名称“Problem 1a”。
  在标准中，使用 (“IL”、“IN”、“OH”、“MI”)
• 按 POLICY_NUM 合并“PolicyTable”和“LossTable”（您将需要使用外部联接，保留 PolicyTable 中的所有策略）。
• 基于具有以下属性的“PolicyTable”表创建交叉表查询： * 行标题：类别 * 列标题：免赔额 * 值：平均费率 结果应仅反映以下四个州：IL、MI、OH、WI。提示：“总计”选择应该为变量 State 选择“where”。并且可以使用 in() 语句。

综合项目

• 项目信息 该项目旨在涵盖您在该项目过程中所研究的许多主题。该项目将于 2018 年 4 月 24 日最后一堂课之前提交。
  该项目涉及 Excel 和 Access 的工作。一般来说，处理依赖于Access中的处理，然后在Excel中进行。
  与家庭作业相比，该项目的目的是为项目提供不太具体的指导。本示例中使用的大多数（如果不是全部）公式都可以在我们迄今为止在课堂上介绍的示例和作业中找到。

真实世界场景

• 您是 ABC Insurance Company 的定价精算师，这是一家小型个人保险汽车保险公司，每年保费收入约为 3 亿美元。您的工作职责之一是制定定期费率水平指示，以及调整您的评级因素。您的老板要求您制定一个流程，以简化制定 2011 年指示费率的指示流程。为此，他提供了以下说明以及他希望电子表格的外观。

• 他还要求您为他提供一种单独的方式来关注所有州的纯高端趋势，并与全国 (CW) 趋势进行比较。他想要一个简单的观点

• click 方法来执行此操作，因此您建议使用数据透视图来实现此目的。

• ABC 制定费率指标涉及几个步骤，包括： • 趋势分析 • 根据趋势制定损失预测因素 • 制定指定的免赔额和类别（年龄和性别）因素 • 制定投资收益率 • 制定总体费率指标

• 为了制定费率指示，我们向您提供了以下信息： • IT 部门在固定宽度的文本文件中提供了 2007-2009 年所有保单的详细保费和损失信息。该文件大约有100万条记录，因此必须首先在Access中对其进行处理。 • 您还拥有 Access 数据库中最新的 Fast Track 行业趋势数据的副本。 • 您拥有一个 Excel 电子表格，其中包含公司的股票持有和购买情况，以及这些股票过去 4 年的历史价格。

- 您的费率指示流程将包括以下输出（下面将进行更详细的说明）： • Access 数据库，其中包含可将每个州的输出数据复制到 Excel 中的查询。 • Excel 电子表格显示2007-2009 年平均投资收益率的计算结果。 • Excel 电子表格，在将访问查询和投资收益率的输出粘贴到其中后，计算指示的利率变化。
该电子表格应允许用户将另一个州的访问输出粘贴到 Excel 中，并自动生成指示的费率，而无需任何其他更新。 • 带有数据透视图的Excel 电子表格，可显示CW 趋势和状态趋势。有一个示例说明了速率指示工作表的输出应该是什么样子。

微软访问部分

提供了 Access 数据库。该数据库已包含一个名为 TrendData 的表，其中包含行业趋势数据。我们还向您提供了policydata.txt 上的详细策略数据。文本文件的布局如下： Pos 字段 1-2 键 3-4 州 5-8 自付额 9-14 类别代码 15-18 年 19-24 保费 25 指示保单是否有索赔 26-35 索赔金额

** 关于键字段的注释**

 Please use Access to add a primary key.  The keys field in the input dataset is truncated.  (Thus not unique to each record.)  However, it will not impact your calculations.
 You should import the text file with the policy data into an Access table.

在 Access 中，您应该创建输出以下信息的查询：

• 公司保费/损失信息： STATE（分组依据） YEAR（分组依据） DEDUCT（分组依据） CLASS（分组依据） 保单计数（Count） PREM（总和） CLAIM_IND（总和） LOSS_AMOUNT（总和）

• 您应该将查询设置为包含状态的 where 子句。您可以将状态更改为您正在处理的任何状态。行业快速跟踪趋势信息： STATE (Group by) YYYYQ (Group by) Cov (Group by) CW_CARYEARS (Sum) CW_PDCOUNT (Sum) CW_PDAMT (Sum) STATE_CARYEARS (Sum) STATE_PDCOUNT (Sum) STATE_PDAMT (Sum)

CW 字段是基于所有州的所有数据的摘要。 STATE 摘要字段是特定状态的字段之和。同样，您应该为Where 子句设置查询以指定要输出的状态。

请注意，要在同一查询上同时获取 CW 摘要和 STATE 摘要，您必须合并两个单独查询的输出（一个在州级别，一个在 CW 级别），并按 YYYQ 和 COV 合并结果。

Excel 投资收益率工作表

提供的电子表格有两个表。一张表显示了标准普尔 500 指数股票随时间的变化情况。ABC 公司拥有这些股票的子集。投资部门提供了 2006 年年初 (BOY) 持有的股票以及 2007 年 1 月 1 日、2008 年 1 月 1 日和 2009 年 1 月 1 日购买的股票的摘要。您需要计算2007年、2008年和2009年的投资收益率，以及3年收益率的算术平均值。讲义中包含计算演示。您应该在工作表的投资收益计算中填写电子表格。您在此工作表中计算的值将输入到“速率指示”工作表中。

Excel 费率指示工作表

Access 的输出应粘贴到工作表的“输入数据”选项卡中。请随意将任何索引列添加到此选项卡，以后可能对您有用。您还应该能够在该选项卡上输入州名称，并将生成的州名称流到工作表中的所有工作表标题（因此，如果您粘贴新州的数据，则只需在工作表中更改州名称一次）工作表，而不必更新每张工作表）。请记住，更新状态时不需要进行其他更改。考虑不同状态的查询返回不同行数的可能性。您可能需要对 InputData 表使用比现有状态数据更大的引用。讲义中包含一个示例，说明其他工作表选项卡的 Excel 输出应是什么样子。我列出了一些完成讲义上每张纸的技巧。

趋势工作表

• 从 Fast Track 查询的输出中获取趋势信息。贵公司仅使用行业数据进行趋势分析，并权衡州经验与 CW 经验来制定趋势。

• 使用 LINEST 和 INTERCEPT 公式计算适当的值。请随意将索引 (1,2,3,…) 放入 A 列中作为 X 值。您的 Y 值应该是 Pure Premium 列。请记住，纯保费=损失金额/汽车年数。使用这些值来计算拟合值列。年变化是 4 x 斜率（四个时期）。将年度金额除以最近的拟合值，将其表示为百分比趋势

• 创建一个图表，如讲义中所示，包含 4 个系列、状态和 CW、拟合值和实际值。

• 为所有显示的报道创建一个趋势展览。请记住，您可以通过右键单击完成的第一个选项卡进行复制，然后说移动或复制，然后进行复制。如果
  您对第一个选项卡进行了编码，您应该能够复制它，更改覆盖范围参考，并且不必重复任何剩余的工作。

• 损失预测因子工作表 为每个覆盖范围计算的趋势应纳入此工作表。该电子表格上有可信度权重计算。给予的可信度
  某一州的经验是基于该州最近一段时间的索赔数量。 （例如，如果该州 2010 年第一季度的 BI 索赔数为 123,245；则
  分配的可信度权重应为 0.4。）这些应从趋势工作表或输入数据选项卡上的原始数据中提取。

  加权趋势的公式=状态趋势*可信度权重+CW趋势*（1-可信度权重）。

-您还应该包括最近一段时间的损失金额。这用于计算所有覆盖范围（单元格 H13）的加权平均趋势，基于
州的覆盖范围分布。

• 在工作表的底部，计算了 LPF。 LPF 公式​​ = 1 + 预计年数 * 所选趋势。对于选定的趋势，使用可信度
  从上面加权所有覆盖率趋势。

免赔额和年龄/性别因素工作表

从输入数据选项卡上的公司经验数据中获取所有三年的保单数量、保费和损失信息。计算损失率、指示的变化和指示的比率因子。指示的变化计算显示在电子表格上。指标因子 = 当前因子 x (1 + 指示的变化)。在两个工作表中，将条件格式添加到指示的更改列，以突出显示增加幅度大于 10% 或减少幅度小于 -10% 的单元格。

指示变更工作表

• 从输入数据选项卡上的公司经验数据中提取保费和损失信息。从“Loss Projection Factor”选项卡中拉出 LPF。计算预计损失 = 实际损失 x LPF。

• 使用工作表底部所示变化公式中的 3 年期间的预计损失率。从投资收益率工作表中手动输入投资收益率。对于公式中的其他值，请使用所附示例中的值。

Excel 数据透视图工作表

-您的老板还希望有一种方法来跟踪趋势，而不必完成与设置指示工作表相关的所有工作。您已同意创建一个显示纯粹优质趋势的数据透视图。

- 要生成此数据透视图的源数据，您应该能够使用与生成粘贴到指示工作表中的趋势数据相同的查询。主要区别在于，您应该在执行该查询时删除特定状态。查询应返回所有州的值以及包含 CW 值的列。将查询的输出粘贴到新的 Excel 工作簿中。

创建数据透视图。

数据透视图应具有“覆盖范围”和“状态”的页面字段。时间段 (YYYQ) 应显示在图表底部。图表区域中的数据元素应包括州纯保费和 CW 纯保费。