Actuarial Computing Excel Access VBA Repo
1.0.0
保険数理計算 (MAT 253 、ISU)
このコード ラボでは、VLOOKUPS を使用して、データ タブの最初のテーブルを参照しながら、以下にリストされているクレーム数のテーブルに値を入力することに焦点を当てています。これには、VLOOKUP 関数の 3 番目のパラメーターを完成させるために行内の値を使用することが含まれます。また、絶対セル参照と相対セル参照を正しく使用して、同じ関数を黄色の領域全体にコピーできるようにすることも必要です。
このコード ラボでは、HLOOKUP を使用して、[データ 1] タブの 2 番目のテーブルのデータを使用してテーブルにデータを入力することにも焦点を当てています。 HLOOKUP の 3 番目のパラメーターには、適切な一致キーと使用可能な年のリストを含むベクトルへの配列参照を指定した MATCH 関数を使用します。
このコードには、次の手順が含まれます。
このコード ラボでは、実際のクレームの頻度と実際のクレームの重大度を Y 軸に示すグラフを作成します。
これらの各シリーズのスケールは非常に異なるため、異なるシリーズを示すために 2 つの異なる軸を使用します。
x 軸は周期番号 (列 A) を示します。各シリーズは、接続線を含む点として表示されます。
各シリーズには、必要に応じて頻度または重大度のラベルが付けられます。
IF ステートメントを使用して、「問題 1」タブのリスト内の各人々の保険数理現在価値を計算します。 - APV の計算式 = 額面 * 斧 - 斧は性別と喫煙者のステータスによって異なり、それぞれの場合の 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 で使用できる任意の方法を使用して、パラメーターの推定値を取得できます。
あなたは、年間約 3 億ドルの保険料収入を誇る小規模な個人向け自動車保険会社、ABC Insurance Company の価格計算人です。あなたの仕事の責任の 1 つは、定期的な金利レベルの指標を作成し、評価要素を調整することです。あなたの上司は、2011 年の表示レートを作成するための表示プロセスを合理化するプロセスをまとめるよう依頼しました。そのために、上司は次の指示と、スプレッドシートの希望のシェルを提供しました。
また、すべての州の純粋なプレミアムの傾向を監視し、全国 (CW) の傾向と比較するための別の方法を提供するよう依頼しました。彼は簡単なポイントを言いたいのですが、
これを行うには click メソッドがあるため、この目的のためにピボットグラフを提案しました。
ABC でのレート表示の作成には、次のようないくつかの手順が含まれます。 • トレンド分析 • トレンドに基づく損失予測係数の作成 • 表示された控除要素およびクラス (年齢と性別) 要素の作成 • 投資利回りの作成 • 全体的なレート表示の作成
料金の表示を作成するために、次の情報が提供されています。 • IT 部門は、2007 年から 2009 年のすべての保険の詳細な保険料と損失情報を固定幅のテキスト ファイルで提供しました。このファイルには約 100 万レコードがあるため、最初に Access で処理する必要があります。 • 最新の Fast Track 業界動向データのコピーも Access データベースにあります。 • 会社の株式保有と購入、および過去 4 年間の株式の履歴価格を記載した Excel スプレッドシートもあります。
- レート表示プロセスには、次の出力が含まれます (詳細は以下で説明します)。 • 州ごとに Excel にコピーできるデータを出力するクエリを含む Access データベース。 • 2007 年から 2009 年の平均投資利回りの計算を示す Excel スプレッドシート。 • アクセス クエリの出力と投資利回りを貼り付けた後、示されたレート変化を計算する Excel スプレッドシート。
このスプレッドシートを使用すると、ユーザーは別の州のアクセス出力を Excel に貼り付けることができ、追加の更新を行わずに指定されたレートを自動的に生成できます。 • CW トレンドと状態トレンドの両方を表示するピボットグラフを備えた Excel スプレッドシート。レート表示ワークシートからの出力がどのようになるかを示す例があります。
Access データベースが提供されます。このデータベースには、業界トレンド データを含む TrendData という名前のテーブルがすでに含まれています。詳細なポリシー データも、policydata.txt で提供されています。テキスト ファイルのレイアウトは次のとおりです。 位置フィールド 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 (グループ別) 保険契約数 (カウント) PREM (合計) CLAIM_IND (合計) LOSS_AMOUNT (合計)
状態の where 句が含まれるようにクエリを設定する必要があります。作業中のどの状態にも状態を変更できます。業界ファースト トラック トレンド情報: STATE (グループ化) YYYYQ (グループ化) Cov (グループ化) CW_CARYEARS (合計) CW_PDCOUNT (合計) CW_PDAMT (合計) STATE_CARYEARS (合計) STATE_PDCOUNT (合計) STATE_PDAMT (合計)
CW フィールドは、すべての州のすべてのデータに基づく概要です。 STATE 概要フィールドは、特定の状態のフィールドの合計です。ここでも、Where 句のクエリを設定して、出力される状態を指定する必要があります。
同じクエリで CW サマリーと STATE サマリーの両方を取得するには、2 つの別個のクエリ (1 つは州レベル、もう 1 つは CW レベル) の出力をマージし、その結果を YYYQ と COV でマージする必要があることに注意してください。
提供されているスプレッドシートには 2 つのテーブルがあります。 1 つの表には、S&P 500 に含まれる銘柄の株価の推移が示されています。ABC カンパニーは、これらの銘柄のサブセットを所有しています。投資部門は、2006 年 (BOY) 年初に保有していた株式、および 2007 年 1 月 1 日、2008 年 1 月 1 日、および 2009 年 1 月 1 日に購入した株式の概要を提供しました。 2007 年、2008 年、2009 年の投資利回りと、3 年間の利回りの算術平均を計算する必要があります。計算のデモンストレーションは配布資料に含まれています。ワークシートの投資利回り計算のスプレッドシートに記入する必要があります。このワークシートで計算した値は、レート表示ワークシートに入力されます。
Access からの出力は、ワークシートの [入力データ] タブに貼り付ける必要があります。後で役立つ可能性があるインデックス列をこのタブに自由に追加してください。また、そのタブに州名を入力し、その結果の州名がワークシート内のすべてのワークシート ヘッダーに流れるようにすることもできます (そのため、新しい州のデータを貼り付ける場合、州名を変更する必要があるのは、すべてのシートを更新するのではなく、ワークシートを使用します)。状態を更新する際には、他の変更は必要ないことに注意してください。異なる状態のクエリが異なる行数を返す可能性について考えてください。すでに存在する状態データの場合よりも、InputData テーブルに対してより大きな参照を使用する必要がある場合があります。配布資料には、他のワークシート タブに対する Excel 出力の例が含まれています。配布資料の各シートを完成させるためのヒントをいくつかリストしました。
Fast Track クエリの出力から傾向情報を取得します。あなたの会社は傾向分析に業界データのみを使用し、州の経験と CW の経験を重み付けして傾向を開発しています。
LINEST および INTERCEPT の式を使用して、適切な値を計算します。 X 値の列 A にインデックス (1、2、3、…) を自由に入力してください。 Y 値は Pure Premium 列である必要があります。純粋なプレミアム = 損害額 / 車の年数を覚えておいてください。これらの値を使用して近似値列を計算します。年間変化は、傾きの 4 倍です (4 つの期間)。年間金額を最新の近似値で割ることにより、これを % 傾向として表現します。
配布資料に示されているように、State と CW、当てはめと実際の 4 つの系列を使用してグラフを作成します。
表示されているすべてのカバレッジのトレンド展示を作成します。完了した最初のタブを右クリックし、「移動」または「コピー」と言ってコピーを作成すると、そのタブをコピーできることに注意してください。もし
最初のタブを正しくコーディングすれば、それをコピーしてカバレッジ参照を変更するだけで済み、残りの作業を繰り返す必要はありません。
損失予測係数ワークシート 各カバレッジに対して計算された傾向は、このワークシートに反映されます。このスプレッドシートには、信頼性の重み付け計算があります。に与えられる信頼性
特定の州の実績は、最近の期間におけるその州の保険請求数に基づいています。 (たとえば、州の 2010 年第 1 四半期の BI の請求件数が 123,245 件の場合、
割り当てられる信頼性の重みは 0.4 である必要があります。) これらは、トレンド ワークシートまたは [入力データ] タブの生データから取得する必要があります。
重み付けされたトレンドの式 = 状態トレンド * 信頼性の重み + CW トレンド * (1-信頼性の重み)。
-直近期間の損失額も含める必要があります。これは、すべてのカバレッジ (セル H13) の加重平均傾向を計算するために使用されます。
州の適用範囲の分布。
[入力データ] タブの企業経験データから、3 年間すべての保険契約数、保険料、損失情報を取得します。損失率、指示変化、指示率係数を計算します。指定された変更の計算がスプレッドシートに表示されます。指標係数 = 現在の係数 x (1 + 示された変化)。両方のワークシートで、指定された変更列に条件付き書式を追加して、10% を超える増加または -10% 未満の減少があるセルを強調表示します。
[入力データ] タブの企業エクスペリエンス データから保険料と損失の情報を取得します。 [損失予測係数] タブから LPF を取得します。予測損失 = 実際の損失 x LPF を計算します。
ワークシートの下部に示されている変化式で、3 年間の予測損失率を使用します。投資利回りワークシートから投資利回りを手動で入力します。式内の他の値については、添付の例の値を使用します。
- 上司は、兆候ワークシートの設定に伴うすべての作業を行わなくても、傾向を追跡できる方法も望んでいます。純粋なプレミアム トレンドを示すピボットグラフを作成することに同意しました。
- このピボットグラフのソース データを生成するには、Indications ワークシートに貼り付けた傾向データの生成に使用したものと同じクエリを使用できる必要があります。主な違いは、クエリを実行するときに特定の状態を削除する必要があることです。クエリは、すべての状態の値と、CW 値を含む列を返す必要があります。クエリの出力を新しい Excel ワークブックに貼り付けます。
ピボットグラフには、Coverage と State のページ フィールドが必要です。期間 (YYYQ) がグラフの下部に表示されます。チャート領域のデータ要素には、州の純粋なプレミアムと CW の純粋なプレミアムが含まれている必要があります。
