iInfo 資訊交流: Excel VBA自動產生台指結算日

最新消息

[公告2014/05/30] 如有需要將部落格中,任何一篇文章的程式碼使用在商業用途,請與我聯繫。

[公告2015/04/26] Line版的 iInfo程式與投資應用 群組已上線想加入的朋友們,請先查看 "入群須知" 再與我聯繫 Line : aminwhite5168,加入請告知身分與回答 "入群須知" 的問題。

[公告2017/02/20] 近來有網友詢問 MultiCharts 與 Excel 畫冰火能量圖 (8)MultiCharts 與Excel 畫冰火能量圖(11) --- 更名「台股儀表板」 文章中教學檔案取得方式,有興趣的朋友可透過 Line了解詳情 (請勿以為是免費分享),Line : aminwhite5168。

[公告2018/04/22] 台北 Python + Excel VBA 金融資訊爬蟲課程,於 7/21、7/22 兩天開課,課程如網頁內容 金融資訊爬蟲班:台北班 Python 金融資訊爬蟲、EXCEL VBA 金融資訊爬蟲,5/30 前早鳥優惠票,請盡快把握機會,歡迎券商、大專院校邀約講座。

[公告2018/06/01] 台指能量儀表板教學課程 7/14 (六) 下午 1:30 ~ 4:30,15人開班,課程如網頁內容 台北班:台指能量儀表板

2016年12月25日 星期日

Excel VBA自動產生台指結算日

手邊有一些關於台指期VBA檔案都需要參考到結算日來計算,但結算日常需要手動輸入,今天花了一點時間寫了一個小程式,解決需要手動輸入台指期結算日的日期,這樣只要針對特定的日期去做調整就好。
程式的重點在於程式碼的16、38行的內容,尤其16行宣告為 nCount *0 與 0*0 的2維陣列,因為要讓陣列內容直接放入儲存格中,因此宣告 N*N 與 N*N 的2維陣列。若以N*1的1維陣列,38行中儲存格資料內容只會存入陣列第0個位置的資料,這部分需要多注意。
台指結算日為"每個月第三週的星期三"結算,有了結算日的觀念後,來看以下程式碼就會很快上手。
Sub 自動產生台指結算日()
    Dim dPastdate As Date
    Dim dBasedate As Date
    Dim dCurrentdate As Date
    Dim nCount As Integer, nGap As Integer
    Dim i As Integer, j As Integer
    Dim astrDatelist() As String

    With Workbooks(1).Sheets("結算日")
        .Range(.Cells(2, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 1)).Clear
    End With
    
    '以當月日期為基準前後計算10個月結算日
    nGap = 10
    nCount = nGap * 2 + 1
    ReDim astrDatelist(0 To nCount, 0 To 0)
    
    dPastdate = DateAdd("m", nGap * (-1), Date)
    For i = 0 To nCount
        dBasedate = DateAdd("m", i, DateSerial(Year(dPastdate), Month(dPastdate), 1))
        For j = 14 To 24
            dCurrentdate = dBasedate + j
            If 日期是當月第三週星期三(dCurrentdate) Then
                astrDatelist(i, 0) = Format(Year(dCurrentdate), "0000") & Format(Month(dCurrentdate), "00") & Format(Day(dCurrentdate), "00")
                Exit For
            End If
        Next
    Next
    
    With Workbooks(1).Sheets("結算日")
        For i = 2 To .Cells(Rows.Count, 2).End(xlUp).Row
            For j = LBound(astrDatelist) To UBound(astrDatelist)
                If Mid(astrDatelist(j, 0), 1, 6) = Mid(.Cells(i, 2), 1, 6) Then
                    astrDatelist(j, 0) = .Cells(i, 2)
                End If
            Next
        Next
        .Range(.Cells(2, 1), .Cells(2 + nCount, 1)).Value = astrDatelist
    End With
End Sub

Function 日期是當月第三週星期三(dBasedate As Date) As Boolean
    Dim weekofMonth As Integer, dayofweek As Integer
    日期是當月第三週星期三 = False
    weekofMonth = DatePart("ww", dBasedate) - DatePart("ww", DateSerial(Year(dBasedate), Month(dBasedate), 1))
    dayofweek = weekday(DateSerial(Year(dBasedate), Month(dBasedate), 1), 2)
    If dayofweek <= 3 Or dayofweek = 7 Then
        weekofMonth = weekofMonth + 1
    End If
    If weekofMonth = 3 And DatePart("w", dBasedate, 2) = 3 Then
        日期是當月第三週星期三 = True
    End If
End Function
執行結果:

結算日特性:
  1. 每月第三周星期三。
  2. 每月結算日落在14~24號之間。
  3. 每月1號在星期三之前(含),以當月第三周星期三為結算日。
  4. 每月號在星期日,以當月第三周星期三為結算日。
  5. 每月1號在星期三之後,以當月第四周星期三為結算日。
執行畫面中,有一欄 "特殊結算日",這需手動輸入,目的是若當月結算日未依照 "每個月第三週的星期三" 這樣法則,如結算日遇到年節封關提早、農曆年節提早、7月~8月颱風延後,就需要另外輸入日期,以便日期比對可以完整。

參考資料: