Actuarial Computing Excel Access VBA Repo下載 - Actuarial Computing Excel Access VBA Repo原始碼下載

Actuarial Computing Excel Access VBA Repo

Visual Basic

1.0.0

下載

精算-計算-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 產業趨勢資料的副本。

- 您的費率指示流程將包括以下輸出（下面將進行更詳細的說明）： • 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)MTYEAR

CW 欄位是基於所有州的所有資料的摘要。 STATE 摘要欄位是特定狀態的欄位總和。同樣，您應該為Where 子句設定查詢以指定要輸出的狀態。

請注意，要在同一查詢上同時取得 CW 摘要和 STATE 摘要，您必須合併兩個單獨查詢的輸出（一個在州級別，一個在 CW 級別），並按 YYYQ 和 COV 合併結果。

Excel 投資報酬率工作表

提供的電子表格有兩個表。一張表顯示了標準普爾 500 指數股票隨時間的變化。投資部門提供了 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 年期間的預期損失率。從投資收益率工作表中手動輸入投資收益率。對於公式中的其他值，請使用所附範例中的值。