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"";"

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

大家都知道如何安装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("解压后的文件路径")

大功告成！

第二步是运行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

tqdm用法非常简单，只需在平常循环的对象上套上tqdm函数，就可以看到运行进度啦！

from tqdm import tqdm

for i in tqdm(range(100)):
    i  = i * 2

如果你用的是Jupyter notebook，建议用这个notebook.tqdm函数，或者auto.tqdm

from tqdm.notebook import tqdm
# from tqdm.auto import tqdm
for i in tqdm(range(100)):
    i  = i * 2

这个函数画出的Progress Bar更好看

如果你用的是Pandas apply，也可以用tqdm包显示运行进度哦

代码来源：https://stackoverflow.com/questions/18603270/progress-indicator-during-pandas-operations

import pandas as pd
import numpy as np
from tqdm import tqdm
# from tqdm.auto import tqdm  # for notebooks

df = pd.DataFrame(np.random.randint(0, int(1e8), (10000, 1000)))

# Create and register a new `tqdm` instance with `pandas`
# (can use tqdm_gui, optional kwargs, etc.)
tqdm.pandas()

# Now you can use `progress_apply` instead of `apply`
df.groupby(0).progress_apply(lambda x: x**2)

主要介绍一个包 tqdm。可以显示loop 运行的进度

tqdm documentation

Error handling:

On error resume next

定义变量

Dim strName As String 

Dim intX As Integer, intY As Integer, intZ As Integer 
'在下面的语句中，intX 和 intY 都声明为 **** Variant 类型；只有 intZ 声明为 Integer 类型。! 注意!
Dim intX, intY, intZ As Integer

循环: Do...While

Do While [Condition]
        [Statement~~strikethrough text~~]
        Loop

加快代码运行

Application.Calculation = xlManual
Application.ScreenUpdating = False

[Code]

Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

核心代码如下：

# platform是数据集的名字
# 查看缺失值，如果有，需要填充
platform.isna().sum()

# 简单处理缺失值
platform.fillna(0, inplace = True)

# 拟合数据
from lifelines import CoxPHFitter
cph = CoxPHFitter()
cph.fit(platform, duration_col='Survival_years', event_col='Survival',formula = "factor1 + factor2")

# 查看结果
cph.print_summary()

Cox模型简单介绍

Cox模型是用来做生存分析的。但是也可以用来分析企业的生存状况，以及任何一种有终止状态的过程。

Cox模型的数据准备

数据需要有一列代表观测时间，还有一列代表观测时的状态（一般用0和1表示）。
以人的生存状态举例：观测500个人，如果观测的人是活着的，可以记录观测时人的年龄；如果观测的人已经死去，可以记录死亡时间。

Lifelines的CoxPHFitter包还要求不能有缺失值，所以需要对缺失值进行处理。

研究数据集列介绍

Survival 代表观测时的状态的变量
factor1 可能和死亡有关的因素1
factor2 可能和死亡有关的因素2
Survival_days 观测时死亡/已存活时间

Bokeh的效果图：

用Bokeh解法借鉴了Bokeh documentation中的Stacked and Grouped Chart

import pandas as pd
import numpy as np
# 讀取數據
df = pd.read_excel("./data/Sample.xlsx")

df.columns = [i.strip() for i in df.columns]

# 讀取所有 x
df_unique = df.loc[:,["Month", "Agency"]].drop_duplicates()


# 把X轉化爲Bokeh可讀的格式
zip_factors = zip(df_unique['Month'].tolist(), df_unique['Agency'].tolist())
factors = list(zip_factors)

# 讀取所有可能的 3rd Party Agency
agencies = df['3rd Party Agency'].unique().tolist()

# Set colors
# 這裏要注意，如果agency的數目太多的話，可能會出現color不夠用的情況。這種情況下可以進入這個網址
# https://docs.bokeh.org/en/latest/docs/reference/colors.html?highlight=color#bokeh-colors-groups
# 找Bokeh的顔色包
num_agencies = len(agencies)
from bokeh.colors.groups import purple
colors = [purple[i+1] for i in np.arange(num_agencies).tolist()]

dict_source_data = dict(x = factors)

# 整理數據格式
for ag in agencies:
    temp = df.loc[df["3rd Party Agency"] == ag, :]
    temp1 = df_unique.merge(temp, how = "left",left_on = ["Month", "Agency"], right_on = ["Month", "Agency"] )
    dict_source_data[ag] = temp1.Drawdown.fillna(0).tolist()

# Bokeh 作圖

from bokeh.io import output_file, show
from bokeh.models import ColumnDataSource, FactorRange
from bokeh.plotting import figure
from bokeh.colors.groups import purple

# 這裏可以調整Bokeh的theme 可以選的有
# caliber
# light_minimal
# dark_minimal
from bokeh.themes import built_in_themes
from bokeh.io import curdoc
# caliber
# light_minimal
# dark_minimal
curdoc().theme = 'caliber'

#print(purple[1:2])
output_file("bar_stacked_grouped.html")

source = ColumnDataSource(data=dict_source_data)

p = figure(x_range=FactorRange(*factors), plot_height=500,
           toolbar_location=None, tools="")

p.vbar_stack(agencies, x='x', width=0.9, alpha=0.5,color = colors
             , source=source,
             legend_label=agencies)

# 不用科學計數法顯示y軸
from bokeh.models import BasicTickFormatter
p.yaxis.formatter = BasicTickFormatter(use_scientific=False)

p.y_range.start = 0
p.y_range.end = 2000000
p.x_range.range_padding = 0.1
p.xaxis.major_label_orientation = 1
p.xgrid.grid_line_color = None
p.legend.location = "top_center"
p.legend.orientation = "horizontal"

#show(p)


#p = Bar(df, label='date', values='count', stack='class',  group='user',
#    )

#output_file("bar.html")

show(p)

用ggplot解法如下：

data <- clusteredStackBarData %>% as.data.frame

ggplot(data, aes(y = Drawdown, x = Agency, fill = ThirdParty)) + geom_bar(position = "stack", stat = "identity") + facet_grid(~month)

结果如下图

数据有三列：month, agency，3rd party agency 和 drawdown amount

要求横轴是month，纵轴是drawdown amount，每个month画相对应的agency的总drawdown amount。对不同3rd party agency的要求分层。

要求大概是这个样子：

样本数据附上：
Sample.xlsx

Rethink reporting 对精算报告的重新思考

这里主要是针对非寿险的准备金精算报告。

精算师们发现，现在用的准备金精算报告很多都是很多年前写好的，每年只是更新一下数字，往里面加东西而已。这样虽然省事，但是久而久之，关键信息就很容易被忽略掉。

文章对精算报告提出了下面的建议：

• Invest time in reviewing existing reports. It may be that starting afresh would be more beneficial than carrying forward old reports.
• 重新检查过去的报告。也许从头开始比套用旧报告更好。
• Internal peer-review processes should focus on communications, not just calculations – meaning adequate time must be set aside to decide how to report findings.
• 检查精算报告时应该更多地关注于交流的部分，而不仅仅是计算。这意味着更多的时间需要被用于决定如何报告精算结果。
• Take inspiration from other departments – for example, Solvency and Financial Condition Reports involve several departments writing one document. Could a similar approach be adopted to help make reserving reports more commercial?
• 从别的部门获得启示。比如说，偿付能力和金融状况报告需要很多部门一起写一篇文章。准备金报告能不能采取一个类似的方法来变得更加商业化呢？
• Stakeholders will have differing needs, and so ‘one report fits all’ may not work.
• 报告的读者可能有不同的需求，所以一个报告可能没法满足所有人。
• Aim for a ‘Russian-doll effect’ style of reporting, where layering of results is used when communicating information to stakeholders. For example, when presenting reserves, key findings should be used rather than the whole report, using component reports. This may reduce workload and allow different submission dates.
• Think like a board member, not like a reserving actuary. Move away from granular analysis and consider board interest and the key business decisions that depend on this information.
• 像一个董事会成员，而不是一个准备金精算师一样思考。跳出细节化的分析，从一个更广阔的角度看问题。
• When considering TAS compliance, ensure you’re meeting the ‘clear and comprehensible’ requirement, not simply the ‘comprehensive’ part.
• TAS是Technical Actuarial Standard
• Consider alternative communication methods – do tighter deadlines make the investment in automation of packs via software more appropriate?
• Present the executive summary of reports, allowing more time for looking at commercial impacts. Signpost the full detailed reports, working papers, deep dives etc that may be required as part of an audit trail.
• Consider the decision that is being made by reading the report. Is the reader able to find all the information needed to arrive at the decision?
• Accountability and transparency are essential – the board may be accountable in signing off strategic decisions, but as reserving actuaries we are responsible for ensuring that they have sufficient information to make informed decisions.
• Consider clear signposting of material assumptions and key limitations to help focus the attention of non-executive directors.