岡三RSSで当日の損益を計算するプログラム

IncomeResult.vb

' 銘柄コードと銘柄名
Public ccode As Integer
Public name As String

' 損益
Public income As Long

' 信用新規買, 返済売
Public buy_price As Double
Public buy_amount As Long
Public repay_buy_price As Double
Public repay_buy_amount As Long

' 信用買金利
Public buy_interest As Long

' 信用新規売, 返済買
Public sell_price As Double
Public sell_amount As Long
Public repay_sell_price As Double
Public repay_sell_amount As Long

' 信用売金利
Public sell_interest As Long

' 現物買, 売
Public gen_buy_price As Double
Public gen_buy_amount As Long
Public gen_sell_price As Double
Public gen_sell_amount As Long

' 信用約定代金
Public total_price As Long
' 現物約定代金
Public total_gen_price As Long

' 初期化
Public Sub Class_Initialize()
    income = 0
    
    buy_price = 0
    buy_amount = 0
    repay_buy_price = 0
    repay_buy_amount = 0
    buy_interest = 0
    
    sell_price = 0
    sell_amount = 0
    repay_sell_price = 0
    repay_sell_amount = 0
    sell_interest = 0
    
    gen_buy_price = 0
    gen_buy_amount = 0
    gen_sell_price = 0
    gen_sell_amount = 0
    
    total_price = 0
    total_gen_price = 0
End Sub

'--------------------------------------------------------------------------
' 信用新規買い設定（平均法）
Public Sub SetBuy(price As Long, amount As Long)
    total_price = total_price + price * amount
    buy_interest = buy_interest + (price * amount * 0.028 / 365)
    buy_price = buy_price * buy_amount + price * amount
    buy_amount = buy_amount + amount
    buy_price = buy_price / buy_amount
End Sub

' 信用新規売り設定
Public Sub SetSell(price As Long, amount As Long)
    total_price = total_price + price * amount
    sell_interest = sell_interest + (price * amount * 0.0115 / 365)
    sell_price = sell_price * sell_amount + price * amount
    sell_amount = sell_amount + amount
    sell_price = sell_price / sell_amount
End Sub

' 現物買い設定
Public Sub SetGenBuy(price As Long, amount As Long)
    total_gen_price = total_gen_price + price * amount
    gen_buy_price = gen_buy_price * gen_buy_amount + price * amount
    gen_buy_amount = gen_buy_amount + amount
    gen_buy_price = gen_buy_price / gen_buy_amount
End Sub

'--------------------------------------------------------------------------
' 信用返済売り
Public Sub RepayBuy(price As Long, amount As Long)
    total_price = total_price + price * amount
    If buy_amount >= amount And buy_amount > 0 Then
        income = income + (price * amount - buy_price * amount)
        buy_amount = buy_amount - amount
    End If
End Sub

' 信用返済買い
Public Sub RepaySell(price As Long, amount As Long)
    total_price = total_price + price * amount
    If sell_amount >= amount And sell_amount > 0 Then
        income = income + (sell_price * amount - price * amount)
        sell_amount = sell_amount - amount
    End If
End Sub

' 現物返済
Public Sub RepayGenBuy(price As Long, amount As Long)
    total_gen_price = total_gen_price + price * amount
    If gen_buy_amount >= amount And gen_buy_amount > 0 Then
        income = income + (price * amount - gen_buy_price * amount)
        gen_buy_amount = gen_buy_amount - amount
    End If
End Sub

okasanrss.vb

' 本番用
Sub calc_income()
    do_calc_income ("当日損益計算")
End Sub

' テスト用
Sub calc_income_test()
    do_calc_income ("テスト")
End Sub

' 損益計算実行
Sub do_calc_income(sheet_name As String)
    
    Dim sheet As Worksheet
    Set sheet = ThisWorkbook.Worksheets(sheet_name)
    
    ' カウント
    Dim count As Integer
    Dim rows
    Set rows = sheet.rows("3:1000")
    count = Application.WorksheetFunction.count(sheet.Range("A3:A1000"))
    
    Dim results
    Set results = CreateObject("Scripting.Dictionary")
    
    ' 約定時間順に計算
    For i = count To 1 Step -1
        Dim ccode As Integer
        Dim name As String
        Dim price As Long
        Dim amount As Long
        Dim kind As String
        
        ccode = rows.cells(i, 1).Value
        name = rows.cells(i, 2).Value
        price = rows.cells(i, 3).Value
        amount = rows.cells(i, 4).Value
        kind = rows.cells(i, 5).Value
        
        ' 不要なデータなら飛ばす
        If ccode = 0 Or kind = "信用現引" Or kind = "信用現渡" Then
            GoTo next_count
        End If
        
        ' 設定
        Dim income As IncomeResult
        If Not results.exists(ccode) Then
            Set income = New IncomeResult
            income.ccode = ccode
            income.name = name
            results.Add ccode, income
        Else
            Set income = results.Item(ccode)
        End If
        
        ' 損益情報取得
        If kind = "信用新規買" Then
            income.SetBuy price, amount
        ElseIf kind = "信用新規売" Then
            income.SetSell price, amount
        ElseIf kind = "株式現物買" Then
            income.SetGenBuy price, amount
        ElseIf kind = "信用返済売" Then
            income.RepayBuy price, amount
        ElseIf kind = "信用返済買" Then
            income.RepaySell price, amount
        ElseIf kind = "株式現物売" Then
            income.RepayGenBuy price, amount
        End If
        
        Set results.Item(ccode) = income
next_count:
    Next i
    
    ' シートクリア
    sheet.Range("H3:J1000").Clear
    
    ' 損益計算結果と表示
    Dim row_index As Integer
    Dim total_price As Long ' 信用約定代金
    Dim total_gen_price As Long ' 現物約定代金
    Dim total_income As Long
    
    row_index = 3
    total_price = 0
    total_gen_price = 0
    total_income = 0
    total_interest = 0
    For Each o In results.Items
        ' 信用金利
        total_interest = total_interest + o.buy_interest + o.sell_interest
        
        ' 信用約定金額計算
        total_price = total_price + o.total_price
        
        ' 現物約定金額計算
        total_gen_price = total_gen_price + o.total_gen_price
        
        ' 表示
        sheet.cells(row_index, 8).Value = o.ccode & " " & o.name
        sheet.cells(row_index, 9).Value = Format(o.income, "#,##0")
        
        total_income = total_income + o.income
        
        row_index = row_index + 1
    Next
    sheet.cells(row_index, 8).Value = "合計"
    sheet.cells(row_index, 9).Value = Format(total_income, "#,##0")
    row_index = row_index + 1
    
    Dim div_p As Long
    Dim mod_p As Long
    Dim com_p As Long
    
    ' 現物定額手数料
    Dim gen_commission As Long
    gen_commission = 0
    If total_gen_price > 0 Then
        If total_gen_price <= 100000 Then
            gen_commission = 99
        ElseIf total_gen_price <= 200000 Then
            gen_commission = 200
        ElseIf total_gen_price <= 300000 Then
            gen_commission = 300
        ElseIf total_gen_price <= 500000 Then
            gen_commission = 420
        ElseIf total_gen_price <= 1000000 Then
            gen_commission = 780
        Else
            div_p = (total_gen_price - 1000000) \ 1000000
            mod_p = (total_gen_price - 1000000) Mod 1000000
            com_p = div_p * 420
            If mod_p > 0 Then
                com_p = com_p + 420
            End If
            gen_commission = 780 + com_p
        End If
    End If
    
    ' 信用定額手数料（Normal）
    Dim commission As Long
    commission = 0
    If total_price > 0 Then
        If total_price <= 100000 Then
            commission = 99
        ElseIf total_price <= 500000 Then
            commission = 200
        ElseIf total_price <= 1000000 Then
            commission = 315
        ElseIf total_price <= 2000000 Then
            commission = 630
        Else
            div_p = (total_price - 2000000) \ 1000000
            mod_p = (total_price - 2000000) Mod 1000000
            com_p = div_p * 315
            If mod_p > 0 Then
                com_p = com_p + 315
            End If
            commission = 630 + com_p
        End If
    End If
    
    sheet.cells(row_index, 8).Value = "信用約定代金"
    sheet.cells(row_index, 9).Value = Format(total_price, "#,##0")
    row_index = row_index + 1
    sheet.cells(row_index, 8).Value = "信用手数料"
    sheet.cells(row_index, 9).Value = Format(-commission, "#,##0")
    row_index = row_index + 1
    sheet.cells(row_index, 8).Value = "信用金利"
    sheet.cells(row_index, 9).Value = Format(-total_interest, "#,##0")
    row_index = row_index + 1
    
    sheet.cells(row_index, 8).Value = "現物約定代金"
    sheet.cells(row_index, 9).Value = Format(total_gen_price, "#,##0")
    row_index = row_index + 1
    sheet.cells(row_index, 8).Value = "現物手数料"
    sheet.cells(row_index, 9).Value = Format(-gen_commission, "#,##0")
    row_index = row_index + 1
    
    Dim total_commision As Long
    total_commision = gen_commission + commission
    sheet.cells(row_index, 8).Value = "合計金利・手数料"
    sheet.cells(row_index, 9).Value = Format(-total_commision - total_interest, "#,##0")
    row_index = row_index + 1
    
    Dim total_tax As Long
    total_tax = (total_income - total_commision - total_interest) * 0.1
    If total_tax < 0 Then
        total_tax = 0
    End If
    sheet.cells(row_index, 8).Value = "譲渡益税"
    sheet.cells(row_index, 9).Value = Format(-total_tax, "#,##0")
    row_index = row_index + 1
    
    
    ' 本日収支
    sheet.cells(row_index, 8).Value = "本日収支（諸経費引き後）"
    sheet.cells(row_index, 9).Value = Format(total_income - total_commision - total_interest - total_tax, "#,##0")
    
    ' ブログパーツ
    Call blog_parts(sheet, results, total_income, total_commision, total_interest, total_tax)
    
End Sub

' ブログに貼り付ける用のテーブルを作成
Sub blog_parts(sheet As Worksheet, results, income, commision, interest, tax)
    Dim code As String
    code = "<table class='trade_result'>"
    
    Dim color As String
    For Each o In results.Items
        ' 損益に色付け
        If o.income < 0 Then
            color = "<span class=""deco"" style=""color:#0000FF;"">" & Format(o.income, "#,##0") & "</span>"
        Else
            color = "<span class=""deco"" style=""color:#FF0000;"">+" & Format(o.income, "#,##0") & "</span>"
        End If
        
        code = code & "<tr><td>" & o.ccode & " " & o.name & "</td><td class='trade_price'>" & color & "</td></tr>"
    Next
    
    ' 合計
    If income < 0 Then
        color = "<span class=""deco"" style=""color:#0000FF;"">" & Format(income, "#,##0") & "</span>"
    Else
        color = "<span class=""deco"" style=""color:#FF0000;"">+" & Format(income, "#,##0") & "</span>"
    End If
    code = code & "<tr><td>合計</td><td class='trade_price'>" & color & "</td></tr>"
        
    ' 本日収支
    Dim last_income As Integer
    Dim color_result_income As String
    result_income = income - commision - interest - tax
    If result_income < 0 Then
        color_result_income = "<span class=""deco"" style=""color:#0000FF;"">" & Format(result_income, "#,##0") & "</span>"
    Else
        color_result_income = "<span class=""deco"" style=""color:#FF0000;"">+" & Format(result_income, "#,##0") & "</span>"
    End If
    code = code & "<tr><td>本日収支（諸経費引き後）</td><td class='trade_price'>" & color_result_income & "</td></tr>"

    code = code & "</table>"
    sheet.cells(3, 11).Value = code
    
End Sub

' 注文照会情報更新
Sub update_order()
    Dim sheet As Worksheet
    Set sheet = ThisWorkbook.Worksheets("当日損益計算")
    sheet.Range("A3:E1000").Clear
    Application.CommandBars("岡三RSS").Controls(5).Execute
End Sub

' データクリア
Sub clear_income()
    Dim sheet As Worksheet
    Set sheet = ThisWorkbook.Worksheets("当日損益計算")
    sheet.Range("A3:E1000").Clear
    sheet.Range("H3:J1000").Clear
    sheet.Range("K3:K50").Clear
End Sub