Actuarial Computing Excel Access VBA Repo
1.0.0
보험계리 컴퓨팅(MAT 253, ISU)
이 Codelab에서는 VLOOKUPS를 사용하여 데이터 탭의 첫 번째 테이블을 참조하여 청구 건수 아래에 나열된 테이블을 작성하는 데 중점을 둡니다. VLOOKUP 함수의 세 번째 매개 변수를 완성하기 위해 행의 값을 사용하는 작업이 포함됩니다. 또한 전체 노란색 영역에 걸쳐 동일한 기능을 복사할 수 있도록 절대 및 상대 셀 참조를 올바르게 사용하는 것도 포함됩니다.
또한 이 Codelab에서는 HLOOKUP을 사용하여 데이터 1 탭의 두 번째 테이블의 데이터를 사용하여 테이블을 채우는 데 중점을 둡니다. HLOOKUP의 세 번째 매개변수의 경우 적절한 일치 키와 사용 가능한 연도 목록이 있는 벡터에 대한 배열 참조가 있는 MATCH 함수를 사용합니다.
코드에는 아래 지침을 따르는 것이 포함됩니다.
이 Codelab에서는 Y축에 실제 청구 빈도와 실제 청구 심각도를 표시하는 그래프를 만듭니다.
각 계열의 척도가 매우 다르기 때문에 두 개의 서로 다른 축을 사용하여 서로 다른 계열을 표시합니다.
x축은 기간 #(클론 A)을 나타냅니다. 각 계열은 연결 선이 있는 점으로 표시됩니다.
각 시리즈에는 빈도나 심각도가 적절하게 표시되어 있습니다.
IF 문을 사용하여 "문제 1" 탭의 목록에 있는 각 사람의 보험 통계적 현재 가치를 계산합니다. - APV 공식 = 액면가 * 도끼 - 도끼는 성별과 흡연자 상태에 따라 다르며 각 사례에 대한 4개의 탭에서 찾을 수 있습니다. 답변을 확인하려면 첫 번째 정책의 결과에 APV = 1,1238.0이 있어야 합니다. "문제 1" 탭의 A열에는 Policy_Num, Effective_Date, Expiration_Date, Premium 등 4개 필드가 연결된 텍스트 문자열이 포함되어 있습니다. 쉼표(,)를 구분 기호로 사용하여 4개의 열로 구분합니다. Excel 내의 모든 도구나 기능을 사용하여 이를 수행할 수 있습니다.
우리는 '컬렉션' 탭(범위 A1:D2771)의 데이터에서 "문제 1"이라는 새 워크시트에 피벗 테이블 보고서를 설정했습니다. 행 레이블에 '수집 수'를 입력하고 4개의 열을 만듭니다. 1. 프리미엄 합계 2. 손실 합계 3. 손실 비율 = 손실 / 프리미엄 4. 정책 개수, 열의 %로 표시됩니다.
'회귀' 탭에서 단순 선형 회귀(y=a+bx) 기법을 사용하여 사람의 키를 사용하여 체중을 예측합니다. Excel에서 사용할 수 있는 모든 방법을 사용하여 모수 추정치를 얻을 수 있습니다.
귀하는 연간 보험료 수익이 약 $3억에 달하는 소규모 개인 보험 자동차 보험사인 ABC Insurance Company의 가격 계리사입니다. 귀하의 직무 중 하나는 주기적인 요율 수준 표시를 개발하고 평가 요소를 조정하는 것입니다. 귀하의 상사는 2011년 표시 요율 개발을 위한 표시 프로세스를 간소화하는 프로세스를 함께 구성해 달라고 요청했습니다. 이를 위해 상사는 다음 지침과 원하는 스프레드시트 모양에 대한 셸을 제공했습니다.
그는 또한 모든 주에서 순수 프리미엄 트렌드를 관찰하고 전국(CW) 트렌드와 비교할 수 있는 별도의 방법을 제공해달라고 요청했습니다. 그는 간단한 요점을 원하고
이를 수행하려면 클릭 방법을 사용하므로 이 목적으로 피벗 차트를 제안했습니다.
ABC에서 요율 표시 개발에는 다음을 포함한 몇 가지 단계가 포함됩니다. • 추세 분석 • 추세를 기반으로 한 손실 예측 요소 개발 • 표시된 공제액 및 클래스(연령 및 성별) 요소 개발 • 투자 수익률 개발 • 전체 요율 표시 개발
요율 표시를 개발하기 위해 다음 정보가 제공되었습니다. • IT 부서는 2007-2009년 모든 보험 상품에 대한 자세한 프리미엄 및 손실 정보를 고정 폭 텍스트 파일로 제공했습니다. 이 파일에는 약 100만 개의 레코드가 있으므로 먼저 Access에서 처리해야 합니다. • 또한 Access 데이터베이스에 최신 Fast Track 업계 동향 데이터 사본이 있습니다. • 회사의 주식 보유 및 구매는 물론 지난 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 요약을 모두 얻으려면 두 개의 개별 쿼리(상태 수준에서 하나, CW 수준에서 하나)의 출력을 병합하고 결과를 YYYQ 및 COV로 병합해야 합니다.
제공된 스프레드시트에는 두 개의 테이블이 있습니다. 한 테이블에는 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개 기간 동안). 연간 금액을 가장 최근 적합치로 나누어 % 추세로 표현합니다.
4가지 계열, 상태 및 CW, 적합 및 실제를 사용하여 유인물에 표시된 대로 그래프를 만듭니다.
표시된 모든 적용 범위에 대한 트렌드 전시회를 만듭니다. 완료한 첫 번째 탭을 마우스 오른쪽 버튼으로 클릭하고 이동 또는 복사라고 말한 다음 복사본을 만들면 복사할 수 있다는 점을 기억하세요. 만약에
첫 번째 탭을 올바르게 코딩하면 복사하고 적용 범위 참조를 변경할 수 있으며 나머지 작업을 반복할 필요가 없습니다.
손실 예측 계수 워크시트 각 적용 범위에 대해 계산된 추세를 이 워크시트로 가져와야 합니다. 이 스프레드시트에는 신뢰성 가중치 계산이 있습니다. 에게 주어진 신뢰성
특정 주의 경험은 가장 최근 기간 동안 해당 주의 청구 건수를 기반으로 합니다. (예를 들어, 2010년 1분기 주 BI에 대한 청구 건수가 123,245인 경우
할당된 신뢰도 가중치는 0.4여야 합니다.) 추세 워크시트나 입력 데이터 탭의 원시 데이터에서 가져와야 합니다.
가중 추세 공식 = 상태 추세 * 신뢰도 가중치 + CW 추세 * (1-신뢰도 가중치).
- 가장 최근 기간의 손실액도 포함해야 합니다. 이는 다음을 기준으로 모든 적용 범위(셀 H13)에 대한 가중 평균 추세를 계산하는 데 사용됩니다.
주의 보장 분포.
입력데이터 탭의 회사 경험 데이터에서 3개년 전체의 보험 건수, 보험료, 손실 정보를 확인하세요. 손실률, 표시된 변화 및 표시된 비율 요소를 계산합니다. 표시된 변경 계산이 스프레드시트에 표시됩니다. 지표 요인 = 현재 요인 x (1 + 표시된 변화). 두 워크시트 중 하나에서 표시된 변경 열에 조건부 형식을 추가하여 10%보다 큰 증가 또는 -10% 미만의 감소가 있는 셀을 강조 표시합니다.
입력 데이터 탭의 기업 경험 데이터에서 프리미엄 및 손실 정보를 가져옵니다. Loss Projection Factor 탭에서 LPF를 가져옵니다. 예상 손실 = 실제 손실 x LPF를 계산합니다.
워크시트 하단에 표시된 변경 공식에 3년 동안 예상 손실률을 사용합니다. 투자 수익률 워크시트에서 투자 수익률을 수동으로 입력하세요. 수식의 다른 값에는 첨부된 예의 값을 사용하세요.
- 상사는 지표 워크시트 설정과 관련된 모든 작업을 수행하지 않고도 추세를 추적할 수 있는 방법을 원합니다. 순수한 프리미엄 추세를 보여주는 피벗 차트를 만드는 데 동의하셨습니다.
- 이 피벗 차트의 원본 데이터를 생성하려면 표시 워크시트에 붙여넣은 추세 데이터를 생성하는 데 사용한 것과 동일한 쿼리를 사용할 수 있어야 합니다. 주요 차이점은 해당 쿼리를 실행할 때 특정 상태를 제거해야 한다는 것입니다. 쿼리는 CW 값이 포함된 열뿐만 아니라 모든 주에 대한 값을 반환해야 합니다. 쿼리 출력을 새 Excel 통합 문서에 붙여넣습니다.
피벗 차트에는 적용 범위 및 상태의 페이지 필드가 있어야 합니다. 차트 하단에 기간(YYYQ)이 표시되어야 합니다. 차트 영역의 데이터 요소에는 주 순수 프리미엄과 CW 순수 프리미엄이 포함되어야 합니다.
