الصفحة الرئيسية>المتعلقة بالبرمجة>فيجوال بيسك
تنزيل

الحوسبة الاكتوارية-Excel-Access-VBA-Repo

الحوسبة الاكتوارية (MAT 253، ISU)

باستخدام فلوكوبس

يركز معمل التعليمات البرمجية هذا على استخدام VLOOKUPS لملء الجداول المدرجة ضمن عدد المطالبات، مع الإشارة إلى الجدول الأول في علامة تبويب البيانات. يتضمن استخدام القيم الموجودة في الصفوف لإكمال المعلمة الثالثة لوظيفة VLOOKUP. ويتضمن أيضًا الاستخدام الصحيح لمرجع الخلايا المطلق والنسبي بحيث يمكن نسخ نفس الوظيفة عبر المنطقة الصفراء بأكملها.

باستخدام هلوكوب

يركز معمل التعليمات البرمجية هذا أيضًا على استخدام HLOOKUP لملء الجداول باستخدام بيانات من جدول ثانٍ في علامة التبويب "البيانات 1". بالنسبة للمعلمة الثالثة من HLOOKUP، نستخدم وظيفة MATCH مع مفتاح المطابقة المناسب ومرجع الصفيف إلى المتجه مع قائمة السنوات المتاحة

الخسارة وحسابات الأقساط

يتضمن الكود اتباع التعليمات التالية:

  • يجب تنسيق أعمدة "مبلغ الخسارة" و"القيمة الصافية" بالعملة الأمريكية ومنزلتين عشريتين.
  • يجب أن يتم تنسيق أعمدة تكرار المطالبة بنسب مئوية مكونة من منزلتين عشريتين.
  • يجب أن يتم تنسيق وحدات التعرض بفاصلة وبدون منازل عشرية.
  • إنشاء تنسيق شرطي للعمود G إذا كان معدل النمو أقل من 0%.
  • يجب أن يكون الخط الخاص بالخلايا التي تستوفي هذا التنسيق الشرطي بالخط الغامق وأن يتم تعبئة النمط باللون الأحمر.
  • إضافة صيغة شرطية ثانية إذا كان معدل النمو يتجاوز 5%.
  • يجب أن يكون الخط الخاص بالخلايا التي تستوفي هذا الشرط باللون BOLD وأن يتم تعبئة نمطها باللون الأخضر.
  • قم بإنشاء عامل تصفية بيانات على البيانات الموجودة في علامة التبويب المشكلة 2.
  • يجب عليك تحديد الصفوف التي يكون فيها عمود STAT هو "MS" وحيث يكون COV هو "COLL".
  • يرجى الاحتفاظ بالفلتر قيد التشغيل عند حفظ ملفك لإعادته إلي.

الرسوم البيانية

في معمل التعليمات البرمجية هذا، نقوم بإنشاء رسوم بيانية توضح تكرار المطالبة الفعلي وخطورة المطالبة الفعلية على المحور ص.
ونظرًا لاختلاف مقياس كل سلسلة من هذه السلسلة، فإننا نستخدم محورين مختلفين لإظهار السلسلة المختلفة.
يُظهر المحور x الفترة # (العمود A). يتم عرض كل سلسلة كنقاط، مع خطوط متصلة.
يتم تصنيف كل سلسلة على أنها تكرار أو شدة حسب الاقتضاء.

استخدام عبارات IF

باستخدام عبارات IF، نقوم بحساب القيمة الحالية الاكتوارية لكل شخص في القائمة في علامة التبويب "المشكلة 1". - صيغة APV = القيمة الاسمية * الفأس - يختلف الفأس حسب الجنس وحالة المدخن ويمكن العثور عليه في علامات التبويب الأربع لكل حالة. للتحقق من الإجابة، يجب أن تكون نتيجة السياسة الأولى APV = 1,1238.0. في علامة التبويب "المشكلة 1"، يحتوي العمود A على سلسلة نصية عبارة عن سلسلة من 4 حقول مختلفة: Policy_Num، وEffective_Date، وExpiration_Date، وPremium. استخدم الفاصلة (،) كمحدد لفصلها إلى 4 أعمدة. يمكنك استخدام أي أداة أو وظيفة داخل Excel للقيام بذلك.

طاولة محورية

قمنا بإعداد تقرير PivotTable في ورقة عمل جديدة تسمى "المشكلة 1" من البيانات الموجودة في علامة التبويب "المجموعة" (النطاق A1:D2771). ضع "رقم المجموعة" في تسميات الصفوف، وقم بإنشاء 4 أعمدة: 1. مجموع الأقساط 2. مجموع الخسارة 3. نسبة الخسارة = الخسارة / القسط 4. عدد السياسات، يتم عرضه كنسبة مئوية من العمود.

الانحدار

في علامة التبويب "الانحدار"، استخدم تقنية الانحدار الخطي البسيط (y=a+bx) للتنبؤ بوزن الشخص باستخدام طوله. يمكنك استخدام أية طرق متوفرة في Excel للحصول على تقديرات المعلمات.

إنشاء استعلام

  • قم بإنشاء استعلام في Access يقوم بإرجاع قائمة بجميع أرقام السياسة في ولايات IL وIN وOH وMI. احفظ هذا الاستعلام بالاسم "المشكلة 1 أ".
    في المعايير، استخدم في (“IL”، “IN”، “OH”، “MI”)
  • دمج "PolicyTable" و"LossTable" بواسطة POLICY_NUM (ستحتاج إلى استخدام صلة خارجية، مع الاحتفاظ بجميع السياسات من PolicyTable).
  • قم بإنشاء استعلام جدولي تبادلي استنادًا إلى جدول "PolicyTable" بالخصائص التالية: * عنوان الصف: الفئة * عنوان العمود: قابل للخصم * القيمة: متوسط ​​السعر يجب أن تعكس النتائج الحالات الأربع التالية فقط: IL، MI، OH، WI. تلميح: يجب أن يكون الاختيار "الإجمالي" هو تحديد "أين" للحالة المتغيرة. ويمكنك استخدام عبارة in ().

مشروع شامل

  • معلومات المشروع يهدف هذا المشروع إلى تغطية العديد من الموضوعات التي عملت عليها أثناء سير هذا المشروع. من المقرر تسليم المشروع قبل الفصل النهائي في 24 أبريل 2018.
    ويتضمن المشروع العمل في Excel، وAccess. بشكل عام، تعتمد المعالجة على المعالجة في برنامج Access، ثم العمل في برنامج Excel.
    الهدف من هذا المشروع هو توفير توجيه أقل تحديدًا للمشروع مقارنةً بالواجب المنزلي. يمكن العثور على معظم الصيغ المستخدمة في هذا المثال، إن لم يكن كلها، في الأمثلة والواجبات المنزلية التي تناولناها حتى الآن في الفصل.

سيناريو العالم الحقيقي

  • أنت خبير اكتواري للتسعير في شركة ABC للتأمين، وهي شركة تأمين سيارات شخصية صغيرة تحقق إيرادات متميزة تبلغ حوالي 300 مليون دولار سنويًا. تتمثل إحدى مسؤوليات وظيفتك في تطوير مؤشرات دورية لمستوى المعدل، بالإضافة إلى تعديلات على عوامل التصنيف الخاصة بك. لقد طلب منك رئيسك إعداد عملية لتبسيط عملية المؤشرات لتطوير المعدلات المشار إليها لعام 2011. وللقيام بذلك، قدم التعليمات التالية بالإضافة إلى غلاف لما يود أن يبدو عليه جدول البيانات.

  • لقد طلب منك أيضًا توفير طريقة منفصلة له لمراقبة الاتجاهات المتميزة النقية في جميع الولايات، ومقارنتها بالاتجاهات على مستوى الدولة (CW). يريد نقطة بسيطة و

  • انقر فوق الأسلوب للقيام بذلك، لذا فقد اقترحت PivotChart لهذا الغرض.

  • يتضمن تطوير مؤشر المعدل في ABC بضع خطوات بما في ذلك: • تحليل الاتجاه • تطوير عوامل توقع الخسارة بناءً على الاتجاهات • تطوير العوامل القابلة للخصم والطبقة (العمر والجنس) المشار إليها • تطوير عائد الاستثمار • تطوير مؤشر المعدل الإجمالي

  • لتطوير مؤشرات الأسعار، تم إعطاؤك المعلومات التالية: • قدم قسم تكنولوجيا المعلومات معلومات تفصيلية عن الأقساط والخسائر لجميع وثائق التأمين للفترة 2007-2009 في ملف نصي ذو عرض ثابت. يحتوي هذا الملف على حوالي مليون سجل، لذا يجب معالجته أولاً في Access. • لديك أيضًا نسخة من أحدث بيانات اتجاهات الصناعة السريعة في قاعدة بيانات Access. • لديك جدول بيانات Excel يتضمن مقتنيات ومشتريات أسهم الشركة، بالإضافة إلى الأسعار التاريخية لتلك الأسهم على مدار السنوات الأربع الماضية.

- ستتضمن عملية مؤشرات المعدل المخرجات التالية (موضحة بمزيد من التفاصيل أدناه): • قاعدة بيانات Access تحتوي على استعلامات تخرج البيانات التي يمكن نسخها إلى برنامج Excel لكل ولاية. • جدول بيانات Excel يوضح حساب متوسط ​​عائد الاستثمار لعام 2007-2009. • جدول بيانات Excel يحسب معدل التغير المشار إليه، بعد لصق مخرجات استعلامات الوصول وعائد الاستثمار فيه.
يجب أن يسمح جدول البيانات هذا للمستخدم بلصق مخرجات الوصول لحالة أخرى في Excel، وإنشاء المعدل المشار إليه تلقائيًا دون أي تحديثات إضافية. • جدول بيانات Excel مزود بمخطط PivotChart يعرض كلا من اتجاه CW واتجاه الحالة. يوجد مثال لما يجب أن يبدو عليه الناتج من ورقة عمل مؤشرات السعر.

جزء وصول مايكروسوفت

يتم توفير قاعدة بيانات Access. تحتوي قاعدة البيانات هذه بالفعل على جدول يسمى TrendData، والذي يحتوي على بيانات اتجاهات الصناعة. لقد تم تزويدك أيضًا ببيانات السياسة التفصيلية على Policydata.txt. تخطيط الملف النصي أدناه: حقل نقاط البيع 1-2 المفاتيح 3-4 الحالة 5-8 القابل للخصم 9-14 رمز الفئة 15-18 السنة 24-19 قسط 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 (تجميع حسب) خصم (تجميع حسب) CLASS (تجميع حسب) عدد السياسات (العدد) PREM (Sum) CLAIM_IND (Sum) LOSS_AMOUNT (Sum)

  • يجب عليك تعيين الاستعلام بحيث يحتوي على عبارة "حيث" للحالة. يمكنك تغيير الحالة إلى أي حالة تعمل عليها. معلومات اتجاه التتبع السريع للصناعة: STATE (مجموعة حسب) YYYYQ (مجموعة حسب) Cov (مجموعة حسب) CW_CARYEARS (Sum) CW_PDCOUNT (Sum) CW_PDAMT (Sum) STATE_CARYEARS (Sum) STATE_PDCOUNT (Sum) STATE_PDAMT (Sum)

حقول CW عبارة عن ملخصات تعتمد على جميع البيانات الخاصة بجميع الولايات. حقول ملخص الحالة هي مجموع الحقول الخاصة بحالة معينة. مرة أخرى، يجب عليك إعداد الاستعلام عن عبارة Where لتحديد الحالة التي سيتم إخراجها.

لاحظ أنه للحصول على ملخصات CW وملخصات STATE على نفس الاستعلام، سيتعين عليك دمج مخرجات استعلامين منفصلين (واحد على مستوى الولاية، والآخر على مستوى CW) ودمج النتائج حسب YYYQ وCOV.

ورقة عمل عائد الاستثمار في Excel

يحتوي جدول البيانات المقدم على جدولين. يحتوي أحد الجداول على أسعار الأسهم بمرور الوقت للأسهم في مؤشر S&P 500. وتمتلك شركة ABC مجموعة فرعية من تلك الأسهم. قدمت إدارة الاستثمار ملخصًا للأسهم المحتفظ بها في بداية العام (BOY) 2006، بالإضافة إلى الأسهم المشتراة في 1/1/2007 و1/1/2008 و1/1/2009. تحتاج إلى حساب عائد الاستثمار للأعوام 2007 و2008 و2009، والمتوسط ​​الحسابي لعائد 3 سنوات. يتم تضمين عرض توضيحي للحساب في النشرة. يجب عليك ملء جدول البيانات الخاص بحساب عائد الاستثمار في ورقة العمل. سيتم إدخال القيمة التي قمت بحسابها في ورقة العمل هذه في ورقة عمل مؤشرات الأسعار.

ورقة عمل مؤشرات معدل Excel

يجب لصق الإخراج من Access في علامة التبويب "بيانات الإدخال" في ورقة العمل. لا تتردد في إضافة أي أعمدة فهرس إلى علامة التبويب هذه والتي قد تكون مفيدة لك لاحقًا. يجب أن تكون أيضًا قادرًا على إدخال اسم الحالة في علامة التبويب هذه والحصول على تدفق اسم الحالة الناتج إلى جميع رؤوس ورقة العمل في ورقة العمل (لذلك إذا قمت بلصق بيانات لحالة جديدة، فلن يتعين عليك سوى تغيير اسم الحالة مرة واحدة في ورقة عمل، بدلاً من الاضطرار إلى تحديث كل ورقة). ضع في اعتبارك أنه لا يلزم إجراء أي تغييرات أخرى عند تحديث الحالة. فكر في إمكانية قيام الاستعلامات الخاصة بحالات مختلفة بإرجاع عدد مختلف من الصفوف. قد تحتاج إلى استخدام مراجع أكبر لجداول InputData مقارنةً ببيانات الحالة الموجودة هناك بالفعل. يوجد في النشرة مثال على الشكل الذي يجب أن تبدو عليه مخرجات Excel بالنسبة لعلامات تبويب ورقة العمل الأخرى. لقد أدرجت بعض النصائح حول إكمال كل ورقة في النشرة.

أوراق عمل الاتجاهات

  • احصل على معلومات الاتجاه من مخرجات استعلام التتبع السريع. تستخدم شركتك بيانات الصناعة فقط لتحليل الاتجاهات، وتزن تجربة الدولة مع تجربة الأسلحة الكيميائية لتطوير اتجاهاتها.

  • استخدم صيغ LINEST وINTERCEPT لحساب القيم المناسبة. لا تتردد في وضع الفهرس (1،2،3،...) في العمود A لقيم X الخاصة بك. يجب أن تكون قيم Y الخاصة بك عبارة عن عمود Pure Premium. تذكر أن القسط النقي = مبلغ الخسارة / سنوات السيارة. استخدم هذه القيم لحساب أعمدة القيم المجهزة. التغير السنوي هو 4 × المنحدر (لأربع فترات). قم بالتعبير عن ذلك في صورة اتجاه % عن طريق قسمة المبلغ السنوي على أحدث قيمة مجهزة

  • قم بإنشاء رسم بياني كما هو موضح في النشرة مع 4 سلاسل، الحالة وCW، المجهزة والفعلية.

  • إنشاء معرض الاتجاه لجميع التغطيات المعروضة. ضع في اعتبارك أنه يمكنك نسخ علامة التبويب الأولى التي أكملتها بالنقر بزر الماوس الأيمن عليها، ثم قل نقل أو نسخ، ثم قم بعمل نسخة. لو
    قمت بترميز علامة التبويب الأولى بشكل صحيح، يجب أن تكون قادرًا فقط على نسخها، وتغيير مرجع التغطية، ولن تضطر إلى تكرار أي من العمل المتبقي.

  • ورقة عمل عامل إسقاط الخسارة يجب أن تمتد الاتجاهات المحسوبة لكل تغطية إلى ورقة العمل هذه. هناك حساب ترجيح للمصداقية في جدول البيانات هذا. المصداقية الممنوحة لل
    تعتمد تجربة دولة معينة على عدد المطالبات الخاصة بتلك الدولة في الفترة الأخيرة. (على سبيل المثال، إذا كان عدد مطالبات الولاية في الربع الأول من عام 2010 لـ BI هو 123,245؛
    يجب أن يكون وزن المصداقية المعين 0.4.) ويجب سحبها إما من أوراق عمل الاتجاه، أو من البيانات الأولية الموجودة في علامة تبويب بيانات الإدخال.

    معادلة الاتجاه المرجح = اتجاه الحالة * وزن المصداقية + اتجاه CW * (1- وزن المصداقية).

-يجب عليك أيضًا تضمين مبلغ الخسارة للفترة الأخيرة. يُستخدم هذا لحساب متوسط ​​الاتجاه المرجح لجميع التغطيات (الخلية H13)، استنادًا إلى
توزيع التغطية على مستوى الولاية.

  • في الجزء السفلي من ورقة العمل، يتم حساب LPF. معادلة LPF = 1 + # عدد السنوات المتوقعة * الاتجاه المحدد. بالنسبة للاتجاه المحدد، استخدم المصداقية
    المرجح كل اتجاه التغطية من فوق.

أوراق عمل الخصم وعامل العمر/الجنس

احصل على معلومات حول عدد البوليصات والأقساط والخسائر لجميع السنوات الثلاث من بيانات تجربة الشركة في علامة تبويب بيانات الإدخال. حساب نسبة الخسارة، والتغير المشار إليه، وعوامل المعدل المشار إليها. يظهر حساب التغيير المشار إليه في جدول البيانات. عامل المؤشر = العامل الحالي x (1 + التغيير المشار إليه). قم بإضافة تنسيق شرطي إلى عمود التغيير المشار إليه في كلتا ورقتي العمل لتمييز الخلايا التي تحتوي على زيادة أكبر من 10%، أو أقل من -10% نقصان.

ورقة عمل التغيير المشار إليها

  • اسحب معلومات الأقساط والخسائر من بيانات تجربة الشركة في علامة تبويب بيانات الإدخال. اسحب LPF من علامة التبويب عامل إسقاط الخسارة. حساب الخسائر المتوقعة = الخسائر الفعلية × LPF.

  • استخدم نسبة الخسارة المتوقعة لفترة 3 سنوات في معادلة التغيير المشار إليها في أسفل ورقة العمل. أدخل عائد الاستثمار يدويًا من ورقة عمل عائد الاستثمار. بالنسبة للقيم الأخرى في الصيغة، استخدم القيم في المثال المرفق.

ورقة عمل Excel PivotChart

- يرغب رئيسك أيضًا في الحصول على طريقة لتتبع الاتجاهات، دون الاضطرار إلى القيام بكل العمل المتضمن في إعداد ورقة عمل المؤشرات. لقد وافقت على إنشاء PivotChart الذي يعرض الاتجاهات المميزة النقية.

-لإنشاء البيانات المصدر لهذا المخطط المحوري، يجب أن تكون قادرًا على استخدام نفس الاستعلام الذي استخدمته لإنشاء بيانات الاتجاه التي قمت بلصقها في ورقة عمل المؤشرات. والفرق الرئيسي هو أنه يجب عليك إزالة الحالة المحددة عند تنفيذ هذا الاستعلام. يجب أن يقوم الاستعلام بإرجاع القيم لجميع الحالات، بالإضافة إلى الأعمدة التي تحتوي على قيم CW. قم بلصق إخراج الاستعلام في مصنف Excel جديد.

إنشاء مخطط محوري.

يجب أن يحتوي PivotChart على حقول الصفحة الخاصة بالتغطية والحالة. يجب أن يتم عرض الفترة الزمنية (YYYQ) في الجزء السفلي من المخطط. يجب أن تشتمل عناصر البيانات الموجودة في منطقة المخطط على قسط الدولة النقي، وعلاوة CW النقية.

يوسع
معلومات إضافية
تطبيقات ذات صلة
نوصي لك
أخبار ذات صلة الكل