iInfo 資訊交流: MultiCharts 匯入外部資料(4) --- MultiCharts以指標方式，動態匯入Excel 產生的CSV檔

2017年3月4日星期六

MultiCharts 匯入外部資料(4) --- MultiCharts以指標方式，動態匯入Excel 產生的CSV檔

前面介紹使用QuoteManager中ASCII Mapping功能來匯入外資、自營商未平倉量的文章，MultiCharts 匯入外部資料(1) --- Excel VBA 產生的外資未平倉量檔案，今天介紹使用MultiCharts指標來呈現外資、自營商未平倉量。
這裡筆者非常感謝陳立偉老師指點幫助才能順利完成這一連串的串接，如果沒有老師的協助，筆者這樣的做法可能還會要花更多的時間。
使用QuoteManager中ASCII Mapping做法的優缺點：

將資料存入QuoteManager，確保資料可以保存下去。
需每日進行手動資料更新，不過這可以靠Windows排程來解決。
資料可由Excel VBA、Python等常用的語言來產生。
需使用Data N。

現在開始介紹使用 MultiCharts指標、Excel VBA、C++ Regular dll來完成開啟MultiCharts後，可以透過Excel自動抓外資、自營商未平倉量，再將資料匯入MultiCharts中畫成指標圖。
作法：

MultiCharts呼叫Excel VBA去期交所抓每日的外資，自營商的未平倉庫存，轉呈CSV檔輸出。
MultiCharts使用C++的DLL讀CSV。
透過MultiCharts指標畫成圖。

這樣就不用手動匯入QM並且可以在每天開起MC後就可以見到每日最新的盤後資料。

該作法優缺點如下：

歷史資料只能抓到前3年，證交所僅提前供3年，並且無法將資料儲存入QM，可另外寫程式同步寫入SQLite中。
依賴期交所，如果期交所的網址變更或修改就要改Excel程式。
DIY DLL出問題需自行修改。

以下就介紹流程的做法
1. 用C++製作一個名為 IO.dll 的Regular dll。

(1) 在IO.cpp輸入以下程式碼。

BOOL APIENTRY _fOpen(char * cFilename)
{ 
 g_fin.open(cFilename, ios::in);
 return g_fin? TRUE : FALSE;
}

void APIENTRY _fClose()
{ 
 g_fin.close();
}

HANDLE APIENTRY _getline(char * cBuffer, int nLength)
{
 return g_fin.getline(cBuffer, nLength);
}

char * APIENTRY _Mid(char * cSourceStr, int nFirst, int nCount)
{
 CString csTemp, csStr;
 csTemp.Format("%s", cSourceStr);
 csStr = csTemp.Mid(nFirst, nCount);
 return (char*)(LPCTSTR)csStr;
}

int APIENTRY _FindS(char * cSourceStr, char * cType)
{
 CString csType = cType;
 return CString(cSourceStr).Find((LPCTSTR)csType);
}

char * APIENTRY _Delete(char * cSourceStr, int nIndex, int nCount)
{
 CString csTemp;
 csTemp.Format("%s", cSourceStr);
 csTemp.Delete(nIndex, nCount);
 return (char*)(LPCTSTR)csTemp;
}

(2) 在 IO.def 輸入以下程式碼。

; IO.def : Declares the module parameters for the DLL.

LIBRARY

EXPORTS
    ; Explicit exports can go here
    _fOpen @1
    _fClose @2
    _getline @3
    _Mid @4
    _FindS @5
    _Delete @6

以上的程式在32位元與64位元環境下皆可使用，筆者僅介紹使用VC6產生的32位元的IO.dll，如需使用64位元，請自行使用Visual Studio 2008以上的版本進行程式編譯。

2. 使用Excel VBA抓取外資、自營商的大台、小台未平倉量。
(1) 將以下程式碼放進Excel VBA的Module1中。

Option Explicit

Const xlFilePath As String = "C:\期貨下載資料\"

Sub 執行(nType As Integer)
    Dim i As Integer
    Dim TXType As String
    Dim IDType As String
    Dim SheetName As String
    
    Application.ScreenUpdating = False
 
    Select Case nType
        Case 1:
            TXType = "TXF"
            IDType = "外資及陸資"
            SheetName = "-Foreign"
        Case 2:
            TXType = "TXF"
            IDType = "自營商"
            SheetName = "-Self"
        Case 3:
            TXType = "MXF"
            IDType = "外資及陸資"
            SheetName = "-Foreign"
        Case 4:
            TXType = "MXF"
            IDType = "自營商"
            SheetName = "-Self"
    End Select
    
    取得未平倉量 TXType, IDType, SheetName
    輸出CSV TXType, IDType, SheetName
    
    Workbooks(1).Save

    Application.ScreenUpdating = True
End Sub

Sub 取得未平倉量(sType As String, IDType As String, SheetName As String)
    Dim url As String
    Dim i, xlCount, nsYear, nsMonth, nsDay, neYear, neMonth, neDay As Integer
    Dim strFilename As String
    Dim Tempsheet
    Dim Rng As Range
    
    If Dir(xlFilePath, vbDirectory) = "" Then
        MkDir (xlFilePath)
    End If
    
    With Workbooks(1).Sheets(sType + SheetName)
        .Cells.ClearContents
    End With
    
    For i = 0 To 5
        nsYear = Format(Year(Date) - 3, "0000")
        nsMonth = Format(Month(Date), "00")
        nsDay = Format(Day(Date), "00")
        
        neYear = Format(Year(Date), "0000")
        neMonth = Format(Month(Date), "00")
        neDay = Format(Day(Date) - i, "00")
        
        url = "http://www.taifex.com.tw/chinese/3/7_12_8dl.asp?goday=&syear=" & nsYear & "&smonth=" & nsMonth & "&sday=" & nsDay & "&eyear=" & neYear & "&emonth=" & neMonth & "&eday=" & neDay & "&COMMODITY_ID=" & sType
    
        strFilename = xlFilePath + "期貨.csv"
        
        下載檔案 url, strFilename
        
        With Workbooks.Open(strFilename)
            If InStr(1, .ActiveSheet.Range("A1"), "DOCTYPE") = 0 Then
                .Close 0
                Exit For
            End If
            .Close 0
        End With
    Next
    
    With Workbooks.Open(strFilename)
        
        Set Rng = .ActiveSheet.UsedRange
                    
        .ActiveSheet.AutoFilterMode = False
    
        With Rng
           .AutoFilter
           .AutoFilter Field:=3, Criteria1:=IDType
        End With
        
        Set Tempsheet = Workbooks(1).Worksheets(sType + SheetName)
         
        xlCount = .ActiveSheet.Range("A65536").End(xlUp).Row
        
        .ActiveSheet.Range("A2:A" & xlCount).Copy Destination:=Tempsheet.Range("A1")
        .ActiveSheet.Range("N2:N" & xlCount).Copy Destination:=Tempsheet.Range("B1")
    
        Set Tempsheet = Nothing
        Set Rng = Nothing
        
        .Close 0
    End With
End Sub

Sub 輸出CSV(sType As String, IDType As String, SheetName As String)
    Workbooks.Add
    Workbooks(1).Sheets(sType + SheetName).Cells.Copy Destination:=Workbooks(2).Sheets(1).Range("A1")
    Application.DisplayAlerts = False
    Workbooks(2).Sheets(1).SaveAs ThisWorkbook.Path & "\" & sType & SheetName & ".csv", FileFormat:=xlCSV, CreateBackup:=False
    Application.DisplayAlerts = True
    Workbooks(2).Close 0
End Sub

Sub 下載檔案(url As String, xlFilename As String)
    Dim oXMLHTTP, objStream As Object
    
    Set oXMLHTTP = CreateObject("Microsoft.XMLHTTP")
    Set objStream = CreateObject("ADODB.stream")
            
    With oXMLHTTP
        .Open "POST", url, False
        .send
        
        If .readyState = 4 Then '.Status = 200
            With objStream
                .Type = 1
                .Open
                .Write oXMLHTTP.ResponseBody
                
                If Dir(xlFilename) <> "" Then Kill xlFilename
                .SaveToFile xlFilename
                .Close
            End With
        End If
    End With

    Set oXMLHTTP = Nothing
    Set objStream = Nothing
End Sub

(2) 將以下程式碼放進Excel VBA的ThisWorkbook中。

Option Base 0
Option Explicit

Private Declare PtrSafe Function GetCommandLine Lib "kernel32" Alias "GetCommandLineW" () As LongPtr
Private Declare PtrSafe Function lstrlenW Lib "kernel32" (ByVal lpString As LongPtr) As Long
Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (MyDest As Any, MySource As Any, ByVal MySize As LongPtr)

#If VBA7 Then
Function CmdToSTr(Cmd As LongPtr) As String
#Else
Function CmdToSTr(Cmd As Long) As String
#End If
    Dim Buffer() As Byte
    Dim StrLen As Long
    If Cmd Then
        StrLen = lstrlenW(Cmd) * 2
        If StrLen Then
            ReDim Buffer(0 To (StrLen - 1)) As Byte
            CopyMemory Buffer(0), ByVal Cmd, StrLen
            CmdToSTr = Buffer
        End If
    End If
End Function

Private Sub Workbook_Open()
    Dim CmdRaw As LongPtr
    Dim CmdLine As String
    Dim sPara As String
    Dim nType As Integer
        
    CmdRaw = GetCommandLine
    CmdLine = CmdToSTr(CmdRaw)
    
    sPara = Right(CmdLine, 1)
    If Not IsNumeric(sPara) Then
            Exit Sub
    End If
    
    nType = CInt(sPara)
    If nType <> 1 And nType <> 2 And nType <> 3 And nType <> 4 Then
        Exit Sub
    End If
    
    Call 執行(nType)
    Application.Quit
End Sub

產生的CSV檔。

3. 使用MultiCharts來呼叫Excel VBA。
(1) 在MultiCharts PowerLanguage Editor寫一個 _ReadFile函數，回傳值型態為數值。

Inputs : istrFileName(StringSimple), iastrData[x,y](StringArrayRef);
var: vBuffer(Spaces(80)), vFileHandle(False), ii(0);

DefineDLLFunc: "C:\IO\Release\IO.dll", bool, "_fOpen", lpstr;
DefineDLLFunc: "C:\IO\Release\IO.dll", void, "_fClose" ;
DefineDLLFunc: "C:\IO\Release\IO.dll", lpstr, "_getline", lpstr, int;
DefineDLLFunc: "C:\IO\Release\IO.dll", lpstr, "_Mid", lpstr, int, int;
DefineDLLFunc: "C:\IO\Release\IO.dll", lpstr, "_Delete", lpstr, int, int;

once cleardebug;

vFileHandle = _fOpen(istrFileName);
if vFileHandle then begin 
 ii = 0;
 while _getline(vBuffer, 80) begin
  iastrData[ii, 1] = _Mid(vBuffer, 0, InStr(vBuffer, ",") - 1);
  iastrData[ii, 2]= _Delete(vBuffer, 0, InStr(vBuffer, ","));
  ii = ii + 1;
 end;
end;

if vFileHandle then _fClose();
_ReadFile = ii - 1;

或使用Windows API編寫。

Inputs : istrFileName(StringSimple), iastrData[x,y](StringArrayRef);

DefineDLLFunc: "kernel32.dll", Long, "_lopen", lpstr, Long;
DefineDLLFunc: "kernel32.dll", Long, "_lread", Long, lpstr, Long;
DefineDLLFunc: "kernel32.dll", Long, "_lclose", Long;

vars: vStr(""), vStrTemp(spaces(80)), vFileNum(0), vDataLen(80), ii(0);

vFileNum = _lopen(istrFileName, 0);

while vDataLen = 80 begin
 vStrTemp = "";
 vDataLen = _lread(vFileNum, vStrTemp, 80);
 print(vDataLen );
 vStr = vStr + MidStr(vStrTemp, 1, vDataLen);
end;

_lclose(vFileNum);

while instr(vStr, ",") <> 0 begin
 iastrData[ii, 1] = MidStr(vStr, 0, instr(vStr, ",") - 1);
 vStr = MidStr(vStr, instr(vStr, ",") + 1, strlen(vStr)); 
 iastrData[ii, 2]= MidStr(vStr, 0, instr(vStr, newline) - 1);
 vStr = MidStr(vStr, instr(vStr, newline) + 1, strlen(vStr));
 ii = ii + 1;
end;

_ReadFile = ii - 1;

第9~18行：將檔案全部內容讀出，存成字串。
為什麼要全部資料存成字串?
原因：Windows API的_lread函數，遇到換行符號"\n"無法自動斷行，因此將所有資料讀出後，組成字串再做解析。
至於為什麼要以80為單位呢?
原因：DOS時代，以80個byte為一行，筆者習慣以80為單位，各位讀者也可以自行決定多少大小為單位。
第20~26行：將資料字串解析，存入陣列中。

(2) 在MultiCharts PowerLanguage Editor寫一個抓取外資大台未平倉量的指標。

input:iName("TXF-Foreign");
vars:vI(0), vStr("");
array:aData[800,2]("");

DefineDLLFunc:"shell32.dll", Long, "ShellExecuteA", Long, lpstr, lpstr, lpstr, lpstr, Long;
DefineDLLFunc:"kernel32", void, "Sleep", Long;

once cleardebug;

if currentbar = 1 then begin
 ShellExecuteA(getappinfo(aiappid), "open", "C:\Program Files\Microsoft Office\Office14\Excel.exe", "C:\Users\Amin\Desktop\MultiCharts-Data.xlsm /e1", "", 5);
 Sleep(2000);
 value1 = _ReadFile("C:\Users\Amin\Desktop\TXF-Foreign.csv", aData);
end;

for vI = 0 to value1 - 1 begin
 if d = JulianToDate(StringToDate(aData[vI, 1])) then begin
  value2=StrToNum(aData[vI, 2]);
  break;
 end;
end;

plot1(value2, "TXF-Foreign", iff(value2> 0, red, green));

將上述指標複製成其他3份指標，名稱自取，分別為
外資大台未平倉量：TXF-Foreign。
外資小台未平倉量：MXF-Foreign。
自營商大台未平倉量：TXF-Self。
自營商小台未平倉量：MXF-Self。
記得裏頭的讀取檔名與路徑皆須修改。

上述程式碼需編譯過，接下來將DLL與Excel放置對應路徑下，開啟MultiCharts 載入指標即可見到效果，可參考以下的最後完成的影片。

以下幾點請自行注意：
1. 指標裡使用WinAPI ShellExecuteA呼叫Excel，請讀者們自行注意Excel 版本，如Excel 2007，請改以"C:\Program Files\Microsoft Office\Office12\Excel.exe"。
64位元 Windows環境下的32位元 Excel 2010，路徑"C:\Program Files (x86)\Microsoft Office\Office14\Excel.exe"。
2. 可將上述MultiCharts指標的第11行換成以Batch file去執行

ShellExecuteA(getappinfo(aiappid), "open", "C:\Users\Amin\Desktop\Excel.bat", "1", "", 5);

或

ShellExecuteA(0, "open", "C:\Users\Amin\Desktop\Excel.bat", "1", "", 5);

並使用Batch。

set para=%1
set path="C:\Program Files\Microsoft Office\Office14"
excel C:\Users\Amin\Desktop\MultiCharts-Data.xlsm /e%para%

3. 抓取外資、自營商未平倉的Excel檔案路徑，請自行修改。
4. ShellExecuteA的用法，請自行參考微軟官網MSDN。

參考資料：

iInfo 資訊交流

最新消息

2017年3月4日星期六

MultiCharts 匯入外部資料(4) --- MultiCharts以指標方式，動態匯入Excel 產生的CSV檔

LinkWithin

最新消息

2017年3月4日 星期六

MultiCharts 匯入外部資料(4) --- MultiCharts以指標方式，動態匯入Excel 產生的CSV檔

LinkWithin

2017年3月4日星期六