60、Excel VBA 实用功能与股票贝塔值计算应用

Excel VBA 实用功能与股票贝塔值计算应用

1. 自定义交易天数函数

在 Excel 中，若没有内置函数来完成特定任务，我们可以自定义函数。例如，要计算两个日期之间的交易天数，可创建如下 TradeDays 函数：

Function TradeDays(firstDate As Date, lastDate As Date) As Integer
' This function returns the number of trading days between two dates.
' It excludes weekends only, although with extra logic, it could be
' changed to exclude other days (such as Christmas). Note how it uses
' Excel's Weekday function, which returns 1 for Sundays, 7 for Saturdays.
Dim nDays As Integer
Dim i As Integer
Dim currentDay As Integer
' Start with the number of days from FirstDate to LastDate
nDays = lastDate - firstDate
TradeDays = nDays
' Now subtract a day for every weekend day.
For i = 1 To nDays
currentDay = Application.W