Skip to content

Instantly share code, notes, and snippets.

@utahta
Last active October 5, 2015 14:17
Show Gist options
  • Save utahta/2818929 to your computer and use it in GitHub Desktop.
Save utahta/2818929 to your computer and use it in GitHub Desktop.
岡三RSSで当日の損益を計算するプログラム
' 銘柄コードと銘柄名
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
' 本番用
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment