以下內容為程序代碼:

1 Private Sub test2()
2 Dim cn As New ADODB.Connection
3 Dim rs As New ADODB.Recordset
4 Dim sql As String
5 Dim index As Integer
6 Dim stklabels(1 To 27) As String
7 Dim initialassets(1 To 27) As Double
8
9 cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=e:\Trade\Report\Report.mdb"
10
11 sql = "select * from 初始權益"
12 rs.Open sql, cn, 3, 1
13
14 'index = rs.RecordCount
15
16 For i = 1 To rs.RecordCount
17 stklabels(i) = rs(0)
18 initialassets(i) = rs(1)
19 rs.MoveNext
20 Next
21
22 rs.Close
23
24 匯總凈利潤 = 0
25 匯總最大回撤 = 0
26 匯總收益風險比 = 0
27
28 For i = 1 To 2
29 初始權益 = 5000000
30 最大權益 = 0
31 回撤 = 0
32 最大回撤 = 0
33 Cells(1 + i, 1) = stklabels(i)
34 sql = "select min(日期) from 當前權益 where 品種='" + stklabels(i) + "' and 日期＞=cdate('2010-04-16')"
35 rs.Open sql, cn, 3, 1
36 Cells(1 + i, 2) = rs(0)
37 Cells(1 + i, 2).NumberFormatLocal = "yyyy/m/d"
38 rs.Close
39
40 sql = "select max(日期) from 當前權益 where 品種='" + stklabels(i) + "' and 日期＞=cdate('2010-04-16')"
41 rs.Open sql, cn, 3, 1
42 Cells(1 + i, 3) = rs(0)
43 Cells(1 + i, 3).NumberFormatLocal = "yyyy/m/d"
44 rs.Close
45
46 sql = "select 當前權益 from 當前權益 where 品種='" + stklabels(i) + "' and 日期＞=cdate('2010-04-16') order by 日期"
47 rs.Open sql, cn, 3, 1
48
49 Do While Not rs.EOF
50 當前權益 = rs(0)
51 If 當前權益 ＞ 最大權益 Then
52 最大權益 = 當前權益
53 Else
54 回撤 = 最大權益 - 當前權益
55 If 回撤 ＞ 最大回撤 Then
56 最大回撤 = 回撤
57 End If
58 End If
59
60 rs.MoveNext
61 Loop
62 凈利潤 = 當前權益 - 初始權益
63 收益風險比 = 凈利潤 / 最大回撤
64 Cells(1 + i, 4) = 凈利潤
65 Cells(1 + i, 5) = 最大回撤
66 Cells(1 + i, 6) = 收益風險比
67 rs.Close
68 Next
69
70 匯總初始權益 = 5000000 * 2
71 匯總最大權益 = 0
72 匯總回撤 = 0
73 匯總最大回撤 = 0
74
75 sql = "select sum(當前權益),日期 from 當前權益 group by 日期 having 日期＞=cdate('2010-04-16') order by 日期"
76 rs.Open sql, cn, 3, 1
77 Do While Not rs.EOF
78 匯總當前權益 = rs(0)
79 If 匯總當前權益 ＞ 匯總最大權益 Then
80 匯總最大權益 = 匯總當前權益
81 Else
82 匯總回撤 = 匯總最大權益 - 匯總當前權益
83 If 匯總回撤 ＞ 匯總最大回撤 Then
84 匯總最大回撤 = 匯總回撤
85 End If
86 End If
87
88 If 匯總最大回撤 ＞= 4000000 Then
89 MsgBox rs(1)
90 End If
91 rs.MoveNext
92 Loop
93
94 匯總凈利潤 = 匯總當前權益 - 匯總初始權益
95 匯總收益風險比 = 匯總凈利潤 / 匯總最大回撤
96 Cells(12, 4) = 匯總凈利潤
97 Cells(12, 5) = 匯總最大回撤
98 Cells(12, 6) = 匯總收益風險比
99 rs.Close
100 End Sub
101
102 Private Sub test()
103 Cells(3, 3) = "cccccc"
104 Dim cn As New ADODB.Connection
105 Dim rs As New ADODB.Recordset
106 Dim sql As String
107 Dim index As Integer
108 Dim stklabels(1 To 27) As String
109 Dim initialassets(1 To 27) As Double
110
111 cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=e:\Trade\Report\Report.mdb"
112
113 sql = "select * from 初始權益"
114 rs.Open sql, cn, 3, 1
115
116 'index = rs.RecordCount
117
118 For i = 1 To rs.RecordCount
119 stklabels(i) = rs(0)
120 initialassets(i) = rs(1)
121 rs.MoveNext
122 Next
123
124 rs.Close
125
126 Cells(3, 3) = "bbbbb"
127
128 For i = 1 To 10
129 Cells(1 + i, 1) = stklabels(i)
130 sql = "select min(日期) from 當前權益 where 品種='" + stklabels(i) + "'"
131 rs.Open sql, cn, 3, 1
132 Cells(1 + i, 2) = rs(0)
133 rs.Close
134
135 sql = "select max(日期) from 當前權益 where 品種='" + stklabels(i) + "'"
136 rs.Open sql, cn, 3, 1
137 Cells(1 + i, 3) = rs(0)
138 rs.Close
139 Next
140 End Sub
141 Private Sub 提取數據_Click()
142 Application.ScreenUpdating = False
143
144 Dim cn As New ADODB.Connection
145 Dim rs As New ADODB.Recordset
146 Dim sql As String
147
148 cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=e:\Trade\Report\Report.mdb"
149
150
151 sql = "select * from 設置"
152 rs.Open sql, cn, 3, 1
153
154 測試品種 = rs(0)
155 測試周期 = rs(1)
156 測試時間 = rs(2)
157 初始資金 = rs(3)
158 保證金率 = rs(4)
159 傭金滑點 = rs(5)
160
161 rs.Close
162
163
164 sql = "select 權益 from 權益 where 日期=(select max(日期) from 權益)"
165 rs.Open sql, cn, 3, 1
166
167 期末權益 = rs(0)
168 盈利金額 = 期末權益 - 初始資金
169 收益率 = 盈利金額 / 初始資金
170
171 rs.Close
172
173
174 sql = "select 平倉盈虧 from 權益"
175 rs.Open sql, cn, 3, 1
176
177 連贏 = 0
178 連虧 = 0
179 最大連贏 = 0
180 最大連虧 = 0
181
182 Do While Not rs.EOF
183 平倉盈虧 = rs(0)
184
185 If 平倉盈虧 ＞ 0 Then
186 連贏 = 連贏 + 1
187 連虧 = 0
188
189 If 連贏 ＞ 最大連贏 Then
190 最大連贏 = 連贏
191 End If
192 End If
193
194 If 平倉盈虧 = 0 Then
195 連贏 = 0
196 連虧 = 0
197 End If
198
199 If 平倉盈虧 ＜ 0 Then
200 連虧 = 連虧 + 1
201 連贏 = 0
202
203 If 連虧 ＞ 最大連虧 Then
204 最大連虧 = 連虧
205 End If
206 End If
207
208 rs.MoveNext
209 Loop
210
211 rs.Close
212
213
214 sql = "select 權益 from 權益"
215 rs.Open sql, cn, 3, 1
216
217 最大回撤 = 0
218 回撤率 = 0
219 最大權益 = 0
220
221 Do While Not rs.EOF
222 權益 = rs(0)
223
224 If 權益 ＞ 最大權益 Then
225 最大權益 = 權益
226 End If
227
228 回撤 = 權益 - 最大權益
229
230 If 回撤 ＜ 最大回撤 Then
231 最大回撤 = 回撤
232 End If
233
234 rs.MoveNext
235 Loop
236
237 rs.Close
238
239
240 sql = "select count(1) from (select distinct 開倉日期 from 交易明細)"
241 rs.Open sql, cn, 3, 1
242
243 交易天數 = rs(0)
244
245 rs.Close
246
247
248 sql = "select count(1) from 權益 where 平倉盈虧＞0"
249 rs.Open sql, cn, 3, 1
250
251 盈利天數 = rs(0)
252
253 rs.Close
254
255
256 sql = "select count(1) from 權益 where 平倉盈虧＜0"
257 rs.Open sql, cn, 3, 1
258
259 虧損天數 = rs(0)
260
261 rs.Close
262
263
264 sql = "select avg(平倉盈虧) from 權益"
265 rs.Open sql, cn, 3, 1
266
267 日均盈利 = rs(0)
268
269 rs.Close
270
271
272 sql = "select avg(平倉盈虧) from 權益 where 平倉盈虧＞0"
273 rs.Open sql, cn, 3, 1
274
275 平均盈利 = rs(0)
276
277 rs.Close
278
279
280 sql = "select avg(平倉盈虧) from 權益 where 平倉盈虧＜0"
281 rs.Open sql, cn, 3, 1
282
283 平均虧損 = rs(0)
284
285 rs.Close
286
287
288 sql = "select count(1) from 權益"
289 rs.Open sql, cn, 3, 1
290
291 觀測天數 = rs(0)
292
293 rs.Close
294
295
296 sql = "select sum(平倉盈虧) from 交易明細"
297 rs.Open sql, cn, 3, 1
298
299 毛利潤 = rs(0)
300
301 rs.Close
302
303
304 sql = "select sum(總手續費) from 交易明細"
305 rs.Open sql, cn, 3, 1
306
307 手續費 = rs(0)
308
309 rs.Close
310
311
312 成功率 = 盈利天數 / 交易天數
313 回撤率 = 最大回撤 / 最大權益
314 回報率 = 平均盈利 / -平均虧損
315 空倉天數 = 觀測天數 - 交易天數
316 出擊率 = 交易天數 / 觀測天數
317 凈利潤 = 毛利潤 - 手續費
318
319 If 毛利潤 ＞ 0 Then
320 傭金率 = 手續費 / 毛利潤
321 Else
322 傭金率 = 0
323 End If
324
325
326 Cells(3, 2) = 測試品種
327 Cells(3, 4) = 測試周期
328 Cells(3, 6) = 測試時間
329 Cells(3, 8) = 初始資金
330 Cells(3, 10) = 保證金率
331 Cells(3, 12) = 傭金滑點
332
333 Cells(6, 2) = 初始資金
334 Cells(7, 2) = 期末權益
335 Cells(8, 2) = 盈利金額
336 Cells(9, 2) = 收益率
337
338 Cells(6, 4) = 最大連贏
339 Cells(7, 4) = 最大連虧
340 Cells(8, 4) = 最大回撤
341 Cells(9, 4) = 回撤率
342
343
344 Cells(6, 6) = 交易天數
345 Cells(7, 6) = 盈利天數
346 Cells(8, 6) = 虧損天數
347 Cells(9, 6) = 成功率
348
349 Cells(6, 8) = 日均盈利
350 Cells(7, 8) = 平均盈利
351 Cells(8, 8) = 平均虧損
352 Cells(9, 8) = 回報率
353
354 Cells(6, 10) = 觀測天數
355 Cells(7, 10) = 交易天數
356 Cells(8, 10) = 空倉天數
357 Cells(9, 10) = 出擊率
358
359 Cells(6, 12) = 毛利潤
360 Cells(7, 12) = 凈利潤
361 Cells(8, 12) = 手續費
362 Cells(9, 12) = 傭金率
363
364
365 sql = "select 日期,累計盈虧 from 權益"
366 rs.Open sql, cn, 3, 1
367
368 Range("y1").CopyFromRecordset rs
369
370 rs.Close
371
372 Range("y:y").NumberFormatLocal = "yyyy/m/d"
373 Range("z:z").NumberFormatLocal = "￥ #,##0"
374
375
376 Set r = Range("a12:l12")
377
378 Dim cht As ChartObject
379 Set cht = ChartObjects.Add(r.Left, r.Top, r.Width, 200)
380 cht.Chart.ChartType = xlArea
381 cht.Chart.ChartStyle = 5
382 cht.Chart.HasLegend = False
383 cht.Chart.SetSourceData Source:=Range("$Y:$Y,$Z:$Z")
384
385
386 sql = "select * from 交易明細 order by 開倉日期 desc"
387 rs.Open sql, cn, 3, 1
388
389 h = 30
390 For i = 1 To rs.RecordCount
391 Cells(h, 1) = i
392 Cells(h, 2) = rs(0)
393 Cells(h, 3) = rs(1)
394 Cells(h, 4) = rs(2)
395 Cells(h, 5) = rs(3)
396 Cells(h, 6) = rs(4)
397 Cells(h, 7) = rs(5)
398 Cells(h, 8) = rs(6)
399 Cells(h, 9) = rs(7)
400 Cells(h, 10) = rs(8)
401 Cells(h, 11) = rs(9)
402 Cells(h, 12) = rs(10)
403
404 Cells(h, 2).NumberFormatLocal = "yyyy/m/d"
405 Cells(h, 4).NumberFormatLocal = "h:mm"
406 Cells(h, 5).NumberFormatLocal = "￥ #,##0"
407 Cells(h, 7).NumberFormatLocal = "h:mm"
408 Cells(h, 8).NumberFormatLocal = "￥ #,##0"
409 Cells(h, 11).NumberFormatLocal = "￥ #,##0"
410 Cells(h, 12).NumberFormatLocal = "￥ #,##0"
411
412 r = "a" & h & ":" & "l" & h
413 Range(r).Font.Bold = True
414 Range(r).HorizontalAlignment = xlCenter
415 Range(r).Borders.LineStyle = xlContinuous
416 rs.MoveNext
417 h = h + 1
418 Next
419
420 rs.Close
421
422
423 cn.Close
424
425
426 Set rs = Nothing
427 Set cn = Nothing
428
429 Application.ScreenUpdating = True
430
431 End Sub
432
433 Private Sub 復制數據_Click()
434 ActiveSheet.Copy after:=Sheets(Sheets.Count)
435 Dim s As Shape
436 For Each s In ActiveSheet.Shapes
437 If s.Type = 8 Or s.Type = 12 Then
438 s.Delete
439 End If
440 Next
441 End Sub
442
443 Private Sub 清除數據_Click()
444 Cells(3, 2).ClearContents
445 Cells(3, 4).ClearContents
446 Cells(3, 6).ClearContents
447 Cells(3, 8).ClearContents
448 Cells(3, 10).ClearContents
449 Cells(3, 12).ClearContents
450
451 Cells(6, 2).ClearContents
452 Cells(7, 2).ClearContents
453 Cells(8, 2).ClearContents
454 Cells(9, 2).ClearContents
455
456 Cells(6, 4).ClearContents
457 Cells(7, 4).ClearContents
458 Cells(8, 4).ClearContents
459 Cells(9, 4).ClearContents
460
461
462 Cells(6, 6).ClearContents
463 Cells(7, 6).ClearContents
464 Cells(8, 6).ClearContents
465 Cells(9, 6).ClearContents
466
467 Cells(6, 8).ClearContents
468 Cells(7, 8).ClearContents
469 Cells(8, 8).ClearContents
470 Cells(9, 8).ClearContents
471
472 Cells(6, 10).ClearContents
473 Cells(7, 10).ClearContents
474 Cells(8, 10).ClearContents
475 Cells(9, 10).ClearContents
476
477 Cells(6, 12).ClearContents
478 Cells(7, 12).ClearContents
479 Cells(8, 12).ClearContents
480 Cells(9, 12).ClearContents
481
482 Range("a30:l9999").Clear
483
484 On Error Resume Next
485
486 ChartObjects(1).Delete
487
488 Range("y:z").Clear
489 End Sub
490

Private Sub test2()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String
Dim index As Integer
Dim stklabels(1 To 27) As String
Dim initialassets(1 To 27) As Double

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=e:\Trade\Report\Report.mdb"

sql = "select * from 初始權益"
rs.Open sql, cn, 3, 1

'index = rs.RecordCount

For i = 1 To rs.RecordCount
stklabels(i) = rs(0)
initialassets(i) = rs(1)
rs.MoveNext
Next

rs.Close

匯總凈利潤 = 0
匯總最大回撤 = 0
匯總收益風險比 = 0

For i = 1 To 2
初始權益 = 5000000
最大權益 = 0
回撤 = 0
最大回撤 = 0
Cells(1 + i, 1) = stklabels(i)
sql = "select min(日期) from 當前權益 where 品種='" + stklabels(i) + "' and 日期>=cdate('2010-04-16')"
rs.Open sql, cn, 3, 1
Cells(1 + i, 2) = rs(0)
Cells(1 + i, 2).NumberFormatLocal = "yyyy/m/d"
rs.Close

sql = "select max(日期) from 當前權益 where 品種='" + stklabels(i) + "' and 日期>=cdate('2010-04-16')"
rs.Open sql, cn, 3, 1
Cells(1 + i, 3) = rs(0)
Cells(1 + i, 3).NumberFormatLocal = "yyyy/m/d"
rs.Close

sql = "select 當前權益 from 當前權益 where 品種='" + stklabels(i) + "' and 日期>=cdate('2010-04-16') order by 日期"
rs.Open sql, cn, 3, 1

Do While Not rs.EOF
當前權益 = rs(0)
If 當前權益 > 最大權益 Then
最大權益 = 當前權益
Else
回撤 = 最大權益 - 當前權益
If 回撤 > 最大回撤 Then
最大回撤 = 回撤
End If
End If

rs.MoveNext
Loop
凈利潤 = 當前權益 - 初始權益
收益風險比 = 凈利潤 / 最大回撤
Cells(1 + i, 4) = 凈利潤
Cells(1 + i, 5) = 最大回撤
Cells(1 + i, 6) = 收益風險比
rs.Close
Next

匯總初始權益 = 5000000 * 2
匯總最大權益 = 0
匯總回撤 = 0
匯總最大回撤 = 0

sql = "select sum(當前權益),日期 from 當前權益 group by 日期 having 日期>=cdate('2010-04-16') order by 日期"
rs.Open sql, cn, 3, 1
Do While Not rs.EOF
匯總當前權益 = rs(0)
If 匯總當前權益 > 匯總最大權益 Then
匯總最大權益 = 匯總當前權益
Else
匯總回撤 = 匯總最大權益 - 匯總當前權益
If 匯總回撤 > 匯總最大回撤 Then
匯總最大回撤 = 匯總回撤
End If
End If

If 匯總最大回撤 >= 4000000 Then
MsgBox rs(1)
End If
rs.MoveNext
Loop

匯總凈利潤 = 匯總當前權益 - 匯總初始權益
匯總收益風險比 = 匯總凈利潤 / 匯總最大回撤
Cells(12, 4) = 匯總凈利潤
Cells(12, 5) = 匯總最大回撤
Cells(12, 6) = 匯總收益風險比
rs.Close
End Sub

Private Sub test()
Cells(3, 3) = "cccccc"
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String
Dim index As Integer
Dim stklabels(1 To 27) As String
Dim initialassets(1 To 27) As Double

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=e:\Trade\Report\Report.mdb"

sql = "select * from 初始權益"
rs.Open sql, cn, 3, 1

'index = rs.RecordCount

For i = 1 To rs.RecordCount
stklabels(i) = rs(0)
initialassets(i) = rs(1)
rs.MoveNext
Next

rs.Close

Cells(3, 3) = "bbbbb"

For i = 1 To 10
Cells(1 + i, 1) = stklabels(i)
sql = "select min(日期) from 當前權益 where 品種='" + stklabels(i) + "'"
rs.Open sql, cn, 3, 1
Cells(1 + i, 2) = rs(0)
rs.Close

sql = "select max(日期) from 當前權益 where 品種='" + stklabels(i) + "'"
rs.Open sql, cn, 3, 1
Cells(1 + i, 3) = rs(0)
rs.Close
Next
End Sub
Private Sub 提取數據_Click()
Application.ScreenUpdating = False

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=e:\Trade\Report\Report.mdb"


sql = "select * from 設置"
rs.Open sql, cn, 3, 1

測試品種 = rs(0)
測試周期 = rs(1)
測試時間 = rs(2)
初始資金 = rs(3)
保證金率 = rs(4)
傭金滑點 = rs(5)

rs.Close


sql = "select 權益 from 權益 where 日期=(select max(日期) from 權益)"
rs.Open sql, cn, 3, 1

期末權益 = rs(0)
盈利金額 = 期末權益 - 初始資金
收益率 = 盈利金額 / 初始資金

rs.Close


sql = "select 平倉盈虧 from 權益"
rs.Open sql, cn, 3, 1

連贏 = 0
連虧 = 0
最大連贏 = 0
最大連虧 = 0

Do While Not rs.EOF
平倉盈虧 = rs(0)

If 平倉盈虧 > 0 Then
連贏 = 連贏 + 1
連虧 = 0

If 連贏 > 最大連贏 Then
最大連贏 = 連贏
End If
End If

If 平倉盈虧 = 0 Then
連贏 = 0
連虧 = 0
End If

If 平倉盈虧 < 0 Then
連虧 = 連虧 + 1
連贏 = 0

If 連虧 > 最大連虧 Then
最大連虧 = 連虧
End If
End If

rs.MoveNext
Loop

rs.Close


sql = "select 權益 from 權益"
rs.Open sql, cn, 3, 1

最大回撤 = 0
回撤率 = 0
最大權益 = 0

Do While Not rs.EOF
權益 = rs(0)

If 權益 > 最大權益 Then
最大權益 = 權益
End If

回撤 = 權益 - 最大權益

If 回撤 < 最大回撤 Then
最大回撤 = 回撤
End If

rs.MoveNext
Loop

rs.Close


sql = "select count(1) from (select distinct 開倉日期 from 交易明細)"
rs.Open sql, cn, 3, 1

交易天數 = rs(0)

rs.Close


sql = "select count(1) from 權益 where 平倉盈虧>0"
rs.Open sql, cn, 3, 1

盈利天數 = rs(0)

rs.Close


sql = "select count(1) from 權益 where 平倉盈虧<0"
rs.Open sql, cn, 3, 1

虧損天數 = rs(0)

rs.Close


sql = "select avg(平倉盈虧) from 權益"
rs.Open sql, cn, 3, 1

日均盈利 = rs(0)

rs.Close


sql = "select avg(平倉盈虧) from 權益 where 平倉盈虧>0"
rs.Open sql, cn, 3, 1

平均盈利 = rs(0)

rs.Close


sql = "select avg(平倉盈虧) from 權益 where 平倉盈虧<0"
rs.Open sql, cn, 3, 1

平均虧損 = rs(0)

rs.Close


sql = "select count(1) from 權益"
rs.Open sql, cn, 3, 1

觀測天數 = rs(0)

rs.Close


sql = "select sum(平倉盈虧) from 交易明細"
rs.Open sql, cn, 3, 1

毛利潤 = rs(0)

rs.Close


sql = "select sum(總手續費) from 交易明細"
rs.Open sql, cn, 3, 1

手續費 = rs(0)

rs.Close


成功率 = 盈利天數 / 交易天數
回撤率 = 最大回撤 / 最大權益
回報率 = 平均盈利 / -平均虧損
空倉天數 = 觀測天數 - 交易天數
出擊率 = 交易天數 / 觀測天數
凈利潤 = 毛利潤 - 手續費

If 毛利潤 > 0 Then
傭金率 = 手續費 / 毛利潤
Else
傭金率 = 0
End If


Cells(3, 2) = 測試品種
Cells(3, 4) = 測試周期
Cells(3, 6) = 測試時間
Cells(3, 8) = 初始資金
Cells(3, 10) = 保證金率
Cells(3, 12) = 傭金滑點

Cells(6, 2) = 初始資金
Cells(7, 2) = 期末權益
Cells(8, 2) = 盈利金額
Cells(9, 2) = 收益率

Cells(6, 4) = 最大連贏
Cells(7, 4) = 最大連虧
Cells(8, 4) = 最大回撤
Cells(9, 4) = 回撤率


Cells(6, 6) = 交易天數
Cells(7, 6) = 盈利天數
Cells(8, 6) = 虧損天數
Cells(9, 6) = 成功率

Cells(6, 8) = 日均盈利
Cells(7, 8) = 平均盈利
Cells(8, 8) = 平均虧損
Cells(9, 8) = 回報率

Cells(6, 10) = 觀測天數
Cells(7, 10) = 交易天數
Cells(8, 10) = 空倉天數
Cells(9, 10) = 出擊率

Cells(6, 12) = 毛利潤
Cells(7, 12) = 凈利潤
Cells(8, 12) = 手續費
Cells(9, 12) = 傭金率


sql = "select 日期,累計盈虧 from 權益"
rs.Open sql, cn, 3, 1

Range("y1").CopyFromRecordset rs

rs.Close

Range("y:y").NumberFormatLocal = "yyyy/m/d"
Range("z:z").NumberFormatLocal = "￥ #,##0"


Set r = Range("a12:l12")

Dim cht As ChartObject
Set cht = ChartObjects.Add(r.Left, r.Top, r.Width, 200)
cht.Chart.ChartType = xlArea
cht.Chart.ChartStyle = 5
cht.Chart.HasLegend = False
cht.Chart.SetSourceData Source:=Range("$Y:$Y,$Z:$Z")


sql = "select * from 交易明細 order by 開倉日期 desc"
rs.Open sql, cn, 3, 1

h = 30
For i = 1 To rs.RecordCount
Cells(h, 1) = i
Cells(h, 2) = rs(0)
Cells(h, 3) = rs(1)
Cells(h, 4) = rs(2)
Cells(h, 5) = rs(3)
Cells(h, 6) = rs(4)
Cells(h, 7) = rs(5)
Cells(h, 8) = rs(6)
Cells(h, 9) = rs(7)
Cells(h, 10) = rs(8)
Cells(h, 11) = rs(9)
Cells(h, 12) = rs(10)

Cells(h, 2).NumberFormatLocal = "yyyy/m/d"
Cells(h, 4).NumberFormatLocal = "h:mm"
Cells(h, 5).NumberFormatLocal = "￥ #,##0"
Cells(h, 7).NumberFormatLocal = "h:mm"
Cells(h, 8).NumberFormatLocal = "￥ #,##0"
Cells(h, 11).NumberFormatLocal = "￥ #,##0"
Cells(h, 12).NumberFormatLocal = "￥ #,##0"

r = "a" & h & ":" & "l" & h
Range(r).Font.Bold = True
Range(r).HorizontalAlignment = xlCenter
Range(r).Borders.LineStyle = xlContinuous
rs.MoveNext
h = h + 1
Next

rs.Close


cn.Close


Set rs = Nothing
Set cn = Nothing

Application.ScreenUpdating = True

End Sub

Private Sub 復制數據_Click()
ActiveSheet.Copy after:=Sheets(Sheets.Count)
Dim s As Shape
For Each s In ActiveSheet.Shapes
If s.Type = 8 Or s.Type = 12 Then
s.Delete
End If
Next
End Sub

Private Sub 清除數據_Click()
Cells(3, 2).ClearContents
Cells(3, 4).ClearContents
Cells(3, 6).ClearContents
Cells(3, 8).ClearContents
Cells(3, 10).ClearContents
Cells(3, 12).ClearContents

Cells(6, 2).ClearContents
Cells(7, 2).ClearContents
Cells(8, 2).ClearContents
Cells(9, 2).ClearContents

Cells(6, 4).ClearContents
Cells(7, 4).ClearContents
Cells(8, 4).ClearContents
Cells(9, 4).ClearContents


Cells(6, 6).ClearContents
Cells(7, 6).ClearContents
Cells(8, 6).ClearContents
Cells(9, 6).ClearContents

Cells(6, 8).ClearContents
Cells(7, 8).ClearContents
Cells(8, 8).ClearContents
Cells(9, 8).ClearContents

Cells(6, 10).ClearContents
Cells(7, 10).ClearContents
Cells(8, 10).ClearContents
Cells(9, 10).ClearContents

Cells(6, 12).ClearContents
Cells(7, 12).ClearContents
Cells(8, 12).ClearContents
Cells(9, 12).ClearContents

Range("a30:l9999").Clear

On Error Resume Next

ChartObjects(1).Delete

Range("y:z").Clear
End Sub