YENLAND TIME :  

 Top >  Library  |  印刷する印刷ページ       はてなブックマークに追加 Buzzurlにブックマーク Yahoo!ブックマークに追加 このページを Google Bookmarks に追加 RSS

大区分

その他

中区分

PC

小区分

データベース

項目

3. エクセルでのBloomberg DDE Serverの使い方

入力者 山下章太 更新日 20071012

概要

BloombergのDDEをエクセルのマクロで利用する方法です。

はじめに

DDEとは、「Dynamic Data Exchange」の略で、ウィキペディア(Wikipedia)では、 『他のアプリケーションとセッションを開始し、サーバアプリケーションにコマンドを送ったり、 応答を受け取ったりすることができる。』とされていますが、 要は、DDEを使うと、他のアプリケーションの機能を利用することが出来ます。

DDE Serverをダイレクトに利用する利点として、取得したいデータが大量な場合、 エクセルリンクだとメモリ不足などの理由で上手く動かないケースがありますが、 このようなケースを回避できます。

DDE Serverを利用してデータを取得する際には、動きは速くありません(かなりスローです)が、 メモリやCPUはほとんど消費しません。

DDE Serverでデータを要求する際には、

Application.DDERequest("channelNumber", "property")
という形式で作成します。

"channelNumber"と"property"は変数です。

Bloomberg関数との比較

Bloombergでウィザードを使って1301極洋の株価リンクを作成した場合、 以下のような式が作成されると思いますが、これと同じ作業をSerever経由で行うだけです。

=BLP|H!'1301 JP Equity,[LAST_PRICE],ST=20040401 END=20041230 PD=D'

『channelNumber』には、接続形式を指定し、『property』には、 DDE Server に要求するデータを指定します。

上の式でいうBLP|Hが『channelNumber』、1301 JP Equity,[LAST_PRICE],ST=20040401 END=20041230 PD=Dが『property』に該当します。

『channelNumber』は、@ヒストリカル・ウィザードやバルク・ウィザードなどの区別、ABloomberg関数のようなものを指定します。

Application.DDEInitiate( A , @ )

これを具体的にヒストリカル・ウィザードと同じ形式で利用しようとすると、

channelNumber = Application.DDEInitiate("BLP", "H")

とします。

ここで、"H"はHistoricalのHです。テーブルウィザードの場合は、"S"を使います(何の略でしょうか?)。

次に『property』ですが、エクセルのリンクで作成された式と同じようなものを入れます。

property = "1301 JP Equity,[LAST_PRICE],ST=20040401 END=20041230 PD=D"

データ要求の利用例

これまでの手順で、ヒストリカルウィザードで2004年4月1日から2004年12月30日までの1301極洋の終値の取得の使用例を記載すると、

@Historical Wizardを利用する

channelNumber = Application.DDEInitiate("BLP", "H")

A取得したい銘柄および期間を選択する

property = "1301 JP Equity,[LAST_PRICE],ST=20040401 END=20041230 PD=D"

BDDE Server へのデータ要求

returnList = Application.DDERequest(channelNumber, property)

という手順になります。

さらに、取得したデータは配列なので、レコード数が固定ではありません。

このレコード数を調べる関数が、UBound()です。

使い方は、

UBound(returnList)
とすると個数が取得できます。

実際の使用例(少し応用編です)

以下では、シート「Data」に取得するティッカーを入れておき、 データ取得開始時と終了時を”評価日From””評価日To”というセルに入れています。

次に、Sybaseのデータベースに接続して、既存のデータベースにあるかどうかを判定して、 データベースに無い場合、データベースにInsertします。
データベース接続について分からない方はこちらをご参照下さい。

'*****変数定義*****
Dim BB(),BB_Premium(), BB_Temp() As Variant
Dim ST_DATE, END_DATE As Date
Dim ST_STR, END_STR, shtname As String
Dim CalcDate, DATE_COUNT As Double
Dim i, j, k, DBconnect, DBexist As Double
Public const ODBCstr = "Provider=MSDASQL.1;Password=yenbridge;Persist Security Info=True;User ID=yenbridge;Data Source=CDS"

'*****データ取得対象期間(Date型)*****
ST_DATE = Range("評価日From")
END_DATE = Range("評価日To")

'*****データ取得対象期間(文字列変換)*****
ST_STR = Year(ST_DATE) & Application.WorksheetFunction.Text(Month(ST_DATE), "00") & Application.WorksheetFunction.Text(Day(ST_DATE), "00")
END_STR = Year(ST_DATE) & Application.WorksheetFunction.Text(Month(END_DATE), "00") & Application.WorksheetFunction.Text(Day(END_DATE), "00")

'*****CDSDのTK数カウント*****
Sheets("Data").Select
Cells(1, 1).Select
RwNo00 = Selection.End(xlDown).Row

'*****表示停止*****
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

'*****配列の再定義*****
ReDim BB(RwNo00 - 2, 7) 'Text型
ReDim BB_Premium(RwNo00 - 2, 2) '数値型

'*****CDSDの基本情報(TKなど)取得*****

For i = 0 To RwNo00 - 2

For j = 0 To 7
BB(i, j) = Sheets("Data").Cells(i + 2, j + 1)
Next
BB_Premium(i, 0) = Sheets("Data").Cells(i + 2, j + 1) 'NENGEN
Next

'*****BLOOMBERG DDE-Server への接続事前処理*****
channelNumber = Application.DDEInitiate("BLP", "H") '接続用のチャンネル
BaseDate = CalcDate
M_date = Year(BaseDate) & Application.WorksheetFunction.Text(Month(BaseDate), "00") & Application.WorksheetFunction.Text(Day(BaseDate), "00")

'*****BB_CDSD からのデータ取得********

'ループ処理で、Exportする
For i = 0 To RwNo00 - 2

'****DDE Server 処理********

'DDE property の作成
property_ask = BB(i, 0) & ",[PX ASK],ST=" & ST_STR & " END=" & END_STR & " PD=D"
property_bid = BB(i, 0) & ",[PX BID],ST=" & ST_STR & " END=" & END_STR & " PD=D"

'DDE Server へのデータ要求
returnList_ask = Application.DDERequest(channelNumber, property_ask)
returnList_bid = Application.DDERequest(channelNumber, property_bid)

If VarType(returnList_ask(2)) <> 8 Or VarType(returnList_bid(2)) <> 8 Then

'*****ODBC接続*****************

'Connectionの設定
Set Con1 = CreateObject("ADODB.Connection")

'Serverへ接続
Con1.Open ODBCstr

'データ取得数のカウント
k = UBound(returnList_ask)

'Temp配列の定義
ReDim BB_Temp(k - 1, 3)

'現在DBにあるデータの抽出
Set Rs1 = CreateObject("ADODB.Recordset")

Sql1 = "SELECT CD.DATE, CD.TS_CODE, CD.SENIORITY, CD.CURRENCY, CD.NENGEN, CD.CE, CD.Name," & _
" CD.PREMIAM_ASK, CD.PREMIAM_BID, CD.PREMIAM_TRADE" & _
" FROM CD_db.CDS CD" & _
" WHERE CD.DATE >= '" & ST_STR & "' AND CD.DATE <= '" & END_STR & "'" & _
" AND CD.TS_CODE Like '" & Trim(BB(i, 1)) & "' AND CD.SENIORITY = '" & BB(i, 2) & "' AND CD.CURRENCY = '" & BB(i, 3) & "'" & _
" AND CD.NENGEN = " & BB_Premium(i, 0) & " AND CD.CE = '" & BB(i, 4) & "' AND CD.Name = '" & BB(i, 7) & "'"

Rs1.Open Sql1, Con1

DBconnect = 0

DBconnect = Rs1.RecordCount

'取得データの配列への格納
For j = 0 To k - 1

BB_Temp(j, 0) = BB(i, 0) 'Ticker
BB_Temp(j, 1) = returnList_ask(j + 1, 1) 'Date
BB_Temp(j, 2) = returnList_ask(j + 1, 2) 'Ask
BB_Temp(j, 3) = returnList_bid(j + 1, 2) 'Bid

'本番Tableへ
If VarType(BB_Temp(j, 2)) <> 8 And VarType(BB_Temp(j, 3)) <> 8 Then

'DB有無の判定
DBDate = Year(BB_Temp(j, 1)) & Application.WorksheetFunction.Text(Month(BB_Temp(j, 1)), "00") & Application.WorksheetFunction.Text(Day(BB_Temp(j, 1)), "00")
Rs1.MoveFirst
DBexist = 0

If DBconnect <> 0 Then

Do Until Rs1.EOF = True Or DBexist = 1
If Rs1!Date = DBDate Then
DBexist = 1
End If

Rs1.MoveNext

Loop

End If

If DBexist = 0 Then

'****データ型の変換******

DBDate = Year(BB_Temp(j, 1)) & Application.WorksheetFunction.Text(Month(BB_Temp(j, 1)), "00") & Application.WorksheetFunction.Text(Day(BB_Temp(j, 1)), "00")
DBSource = BsSht02
DBTS_CODE = BB(i, 1)
DBSENIORITY = BB(i, 2)
DBCURRENCY = BB(i, 3)
DBCE = Application.WorksheetFunction.Text(BB(i, 4), "0")

If VarType(BB(i, 7)) = 8 Then
DBName = BB(i, 7)
Else
DBName = "0"
End If

DBNENGEN = BB_Premium(i, 0)
DBPREMIAM_ASK = BB_Temp(j, 2)
DBPREMIAM_BID = BB_Temp(j, 3)

'***DBへのINSERT

'SQLの作成
Sql2 = "INSERT INTO CD_db.CDS( DATE, SOURCE, TS_CODE, SENIORITY, CURRENCY," & _

" NENGEN, CE, Name, PREMIAM_ASK, PREMIAM_BID )" & _
" SELECT '" & DBDate & "', '" & DBSource & "', '" & DBTS_CODE & "', '" & DBSENIORITY & "', '" & DBCURRENCY & "', " & _
DBNENGEN & ", '" & DBCE & "', '" & DBName & "', " & DBPREMIAM_ASK & ", " & DBPREMIAM_BID

'SQLの実行
Con1.Execute Sql2

End If
End If
Next

'Serverへの接続を切断
Con1.Close: Set Con1 = Nothing

End If
Next


   はてなブックマークに追加 Buzzurlにブックマーク この記事をLivedoorクリップにクリップ! Yahoo!ブックマークに追加 このページを Google Bookmarks に追加 RSS