导航

    精算后花园

    • 注册
    • 登录
    • 搜索
    • 版块
    • 最新
    • 话题
    • 热门
    • 用户
    • 群组
    1. 主页
    2. Mengkelyu
    M
    • 资料
    • 关注
    • 粉丝
    • 主题
    • 帖子
    • 最佳
    • 群组

    Mengkelyu

    @Mengkelyu

    administrators

    3
    声望
    298
    帖子
    47
    资料浏览
    6
    粉丝
    1
    关注
    注册时间 最后登录

    Mengkelyu 关注
    administrators

    Mengkelyu 发布的最佳帖子

    • 如何用SQL语法处理Excel数据? ADO对象在Excel中的处理

      ActiveX Data Objects (ADO) 让我们能够用一个工具来操作SQL Server, Access DB, Worksheet

      ADO有很多用法,首先介绍一个:把excel中的表用SQL语法来处理

      使用ADO流程是

      • 建立连接
      • 写SQL语句
      • 执行SQL语句

      要使用ADO,首先要打开编辑VBA的界面,上面的菜单栏里面有一个是Tools,点击Tools, 里面找到reference。勾选上Microsoft ActiveX Data Objects 6.1 Library(如果没有6.1版本,也可以用2.8版本)

      建立连接的基本语法如下

      Dim Con as new ADODB.Connection
      Con.Open "Connection string" 
              '运行SQL语句
      Con.Close
      

      这里的connection string需要根据不同的Excel版本确定

      https://www.connectionstrings.com/ 这个网站提供了不同情况下需要用到的connection string

      我的excel版本是2016,我用的connection string如下

      strFileName=ThisWorkbook.Fullname
      "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strFileName & "; Extended Properties=""Excel 12.0 Macro;HDR=Yes"";"
      

      就可以成功连接到目前用的工作表啦

      发布在 Excel & VBA
      M
      Mengkelyu
    • 如何安装Github中的R包

      大家都知道如何安装CRAN的R包,只要输入

      install.packages("包名称")
      

      就可以直接从CRAN下载包啦!
      但是有的包CRAN中没有,只有Github版本。这个时候我们需要用一个叫做devtools的工具帮我们的忙啦!
      一般的流程是

      install.packages('devtools')
      
      library(devtools)
      
      devtools::install_github("你要下载的包的github网页路径")
      

      但是我自己实际测试之中出现了如下的Bug:

      Error: Failed to install 'unknown package' from GitHub:
      schannel: failed to receive handshake, SSL/TLS connection failed

      好叭。
      只能手动从Github下载包辽。
      下载好zip文件解压之后之后在R中输入如下指令:

      install("解压后的文件路径")
      

      大功告成!

      发布在 R
      M
      Mengkelyu
    • RE: 如何用SQL语法处理Excel数据? ADO对象在Excel中的处理

      第二步是运行SQL。首先讲讲SELECT要怎么用

      • 创建新对象:Recordset,用于储存SELECT运行出来的结果
      Dim rs As New ADODB.Recordset
      '如果你想要运行SQL的表是一个命了名的区域
      strSQL="SELECT * FROM range"
      ’如果要运行的表是一整个worksheet
      ‘如果数据超过65536行,由于一个excel bug,只能用下面这种方法引用表
      strSQL="SELECT * FROM [Sheet1$]"
      '如果是一个未命名区域
      strSQL="SELECT * FROM [Sheet1$A1:O60]"
      'con是刚刚用到的ADO connection
      rs.Open strSQL, con
      rs.Close
      
      • 把运行出来的结果保存到一个新建的excel表
      Dim ws As Worksheet
              Set ws = output 'Application.Sheets.Add
              ws.Cells.ClearContents
              'Write the header
              Dim i As Long
              For i = 0 To rs.Fields.Count - 1
                  ws.Cells(1, i + 1).Value2 = rs.Fields(i).Name
              Next i
              ws.Range("A2").CopyFromRecordset rs
      

      完整代码如下

      Sub ADOBD_SELECT()
      Dim strFileName As String
      Dim ConnectionString As String
      'Fullname means the folder path is included
      strFileName = ThisWorkbook.FullName
      
      'From website https://www.connectionstrings.com/
      'HDR=Yes -> my data set has heading
      ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strFileName & "; Extended Properties=""Excel 12.0 Macro;HDR=Yes;"";"
      
      Dim conn As New ADODB.Connection
      'Recordset: where the result of the query will be placed
      Dim rs As New ADODB.Recordset
      Dim strSQL As String
      'Write the query
      strSQL = Range("Query")
      conn.Open ConnectionString
              rs.Open strSQL, conn
              Dim ws As Worksheet
              Set ws = output 'Application.Sheets.Add
              ws.Cells.ClearContents
              'Write the header
              Dim i As Long
              For i = 0 To rs.Fields.Count - 1
                  ws.Cells(1, i + 1).Value= rs.Fields(i).Name
              Next i
              ws.Range("A2").CopyFromRecordset rs
              rs.Close
      conn.Close
      Set conn = Nothing
      Set rs = Nothing
      End Sub
      
      发布在 Excel & VBA
      M
      Mengkelyu
    • 如何利用Class module计算精算现值

      小珂珂是 ActuaryGarden 公司的精算实习生,这天,她在做精算表写VBA的时候遇到了一个问题。
      她手上目前有两套精算假设,想利用这两套假设计算年金现值a,还有定期寿险现值A。
      bb3fcfa7-b4f6-49e0-b71f-ed8960aa0982-image.png
      因为不想重复工作,她想到,每一套假设都可以看作一个Object呀!可以利用Class model来避免重复工作。
      可以把利息率和死亡率设置成Class module的属性,计算A和a设置成Class model的方法~

      发布在 Excel & VBA
      M
      Mengkelyu

    Mengkelyu 发布的最新帖子

    • (感谢Judy同学投稿) 经验分析学习笔记(英文)

      感谢Judy同学的分享!

      Introduction

      Experience Analysis is a method that many insurers and reinsurers will adopt to determine some rates based on experience, like mortality and claim utilization rates, which will be used by actuaries in valuation, pricing & so on.
      For product like CI, can use EA to determine the incidence rate & improvement rate based on experience.

      • There’re many different types of EA. Generally speaking, we can split EA studies into two categories by the event it studies:
        • Category I mainly studies decrement which will lead lives leaving the population, including mortality study、lapse study、 A/E study and so on
        • Category II mainly studies events that can occur while life is active in the population that do not result in the life leaving the population, like multiple claims for medical products. We call it a utilization study. Here we’ll do a frequency study, severity study, loss ratio study and so on.

      9ee05cf9-6657-4fed-ae93-f40db852f1db-image.png

      Methodology

      For the mortality study & lapse study in Category I, it’s easy to see that they both focus on decrement rate, but one is for mortality rate, the other one is for lapse rate. In fact, there are many different EA classified by product line and decrement type. Usually, this type of analysis will be carried out across the insurance industry to produce industry standard tables. While some insurance companies will also do this when their data is credible enough. The calculation logic is quite close: use the decrement amount counted at age x divided by the exposure amount at age x. For E.g. use dx/Ex to get the mortality rate. But there’s still a difference in the calculation of exposure amount. (This can be another question).
      For A/E study, it’s usually used by companies with data only credible at the company level. By applying their A/E ratios to experience tables, a company can produce actual rates that match their overall experience. The A/E ratio at each age x is calculated by the actual death amount at age x: dx/ expected death amount at age x. The experience table could be industry-standard table or a standard table produced by the company itself.
      For the Utilization study, it comprises the calculation of Frequency, Severity, Loss Ratio and so on in order to control rates of the event it observes. As for Medical products, there may be multiple claims for prescription drugs. In order to better estimate the expected claim amount, the company may use the historical data to do experience study, estimate: frequency rate over the year of age x: fx= nx( number of claims over age x)/Ex; severity rate over age x: Sx= Cx (total claim amount incurring at age x)/nx.

      • After all, the scope of usage is of great importance when we try to apply A/E to do adjustments. Like for HKA18 REPORT ON HONG KONG ASSURED LIVES MORTALITY 2018 in Hong Kong, it’s produced based on data from fully underwritten, individual & duration >2 policies. So we need to do appropriate adjustments based on our requirements for valuation/pricing targets.
      • Comparing with other products, EA for CI product is a little different. Besides calculating the incidence rate from experience, we also need to estimate the Cause Breakdown of CI product. Due to the Critical illness definitions are not standardized in Hong Kong, if we want to use industry table for pricing/valuation purposes, we need to do further adjustments.
        NB:
      • Death coverage & CI product both focus on incidence rate & improvement rate.
      • CI types:
        (1) standard CI: only cover claim for Critical illness
        (2) accelerated CI: cover both CI claim & death claim – while CI occurs, will settle claims immediately. Later if occurs death, the settlement of claim will depend on whether the Max SA exceeds previous claim.
        (3) additional CI: with 2 coverages: death & CI. Will pay claim separately once the 2 prescribed risk incidents occur.
      发布在 实务交流 (Work experience)
      M
      Mengkelyu
    • RE: SA 2 复习笔记

      SA2.docx
      版本02

      发布在 正精FIA阶段(SP和SA)
      M
      Mengkelyu
    • [调查] 做精算师的大家,对刚刚入行/想入行的学生有什么建议?

      做精算师的大家,对刚刚入行/想入行的学生有什么建议?或者说如果可以和当时的自己对话,现在的你有什么想分享的建议吗?
      欢迎大家畅所欲言

      发布在 实务交流 (Work experience)
      M
      Mengkelyu
    • RE: AI终于要取代精算师了??Excel公式解释,VBA自动编写神器

      解释公式:
      0f69f58e-22bf-4b48-8705-0050b30c63c9-image.png
      28beda6e-40b1-4f98-8092-b337d2af5740-image.png
      af3ea655-ab59-40e5-ae79-cee31ef20f5d-image.png

      发布在 Excel & VBA
      M
      Mengkelyu
    • RE: AI终于要取代精算师了??Excel公式解释,VBA自动编写神器

      f23e9338-86b8-4e84-9fd4-6bb4c72c61de-image.png f9ac60fb-c6e1-4a53-9fe4-1f8ba6d1da89-image.png
      17083812-0c7d-4616-b11c-9c80de0a8e1a-image.png
      最终跑出来的结果:
      f29dacc9-24b0-4b80-b051-8bc2ddef466d-image.png

      发布在 Excel & VBA
      M
      Mengkelyu
    • RE: AI终于要取代精算师了??Excel公式解释,VBA自动编写神器

      44481218-501a-4453-9484-a8ff53f71bb8-image.png 09da97eb-f6b9-4872-9667-9de2f802d6fd-image.png
      fd1af7b6-5108-458d-a88f-9805eff25055-image.png

      发布在 Excel & VBA
      M
      Mengkelyu
    • RE: AI终于要取代精算师了??Excel公式解释,VBA自动编写神器

      e0eabd2c-ee9e-4082-8d25-902324cb610d-image.png 5b29ce76-c0e3-41bb-957d-6663b23f757f-image.png
      56b6d651-60be-4468-9eac-4f878e76c0a2-image.png

      发布在 Excel & VBA
      M
      Mengkelyu
    • RE: AI终于要取代精算师了??Excel公式解释,VBA自动编写神器

      网站的地址是
      https://www.automateexcel.com/online-vba-code-generator/

      发布在 Excel & VBA
      M
      Mengkelyu
    • RE: AI终于要取代精算师了??Excel公式解释,VBA自动编写神器

      f3fb105b-6d64-4ca3-900b-18bef0b64978-image.png e8f473da-7b89-41fb-a6b6-4ce763b0c9b4-image.png
      8b447ff9-bd91-4f55-a316-7df6051e370c-image.png

      发布在 Excel & VBA
      M
      Mengkelyu
    • AI终于要取代精算师了??Excel公式解释,VBA自动编写神器

      这个网站真的超出了我的想象……上一些例子:
      3d443fa9-e2cd-4a9c-b0fb-2729421082e7-image.png
      7e04d9d4-7ad5-4723-8561-6f4da322f1dd-image.png
      4599f68a-04ac-4863-ba69-368426c01b33-image.png
      实际运行
      daf1d964-6c16-4b9b-9382-5357937da5b2-image.png

      发布在 Excel & VBA
      M
      Mengkelyu