エクセルＶＢＡについて教えてください。

質問

株価データ１分足をエクセルにリアルタイムに取り込んで保存しています。
そのデータは時々約定の無い時間帯の抜けている行がありまして、修正して保存したいと思っています。
下に例を示しますと、時間軸の110131と110132が抜けています。
この抜けているところに次の足が刻む時、その抜けているところに直前の終値110130の11637を同時に挿入させたいのです。ＶＢＡで出来ますでしょうか。
エクセル得意ではありませんがエクセルに詳しい方ご指導お願いします。
Windows XP　Microsoft Excel2000

日＆時分　始値　　高値　　　安値　　　終値　　出来高
110128, 11635, 11635, 11634, 11635, 3
110129, 11634, 11634, 11634, 11634, 1
110130, 11636, 11639, 11635, 11637, 5
110133, 11633, 11636 11633, 11633, 3
110134, 11631, 11631, 11631, 11631, 1
110135, 11630, 11630, 11630, 11630, 3
110136, 11629, 11629, 11629, 11629, 3

訂正後
110128, 11635, 11635, 1 1634, 11635, 3
110129, 11634, 11634, 11634, 11634, 1
110130, 11636, 11639, 11635, 11637, 5
110131, 11637, 11637, 11637, 11637, 0
110132, 11637, 11637, 11637, 11637, 0
110133, 11633, 11636, 11633, 11633, 3
110134, 11631, 11631, 11631, 11631, 1
110135, 11630, 11630, 11630, 11630, 3
110136, 11629, 16290, 11629, 11629, 3

回答No.1

tom04 · ベストアンサー

こんばんは！
外していたらごめんなさい。

データはA～F列にあり、1行目はタイトル行で2行目以降にデータがあるとしています。

A列はシリアル値ではなく単なる6桁の数値としてみなしています。

一例です。
画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面が出ますので
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　実行です）

Sub test() 'この行から
Dim i, j As Long
i = 2
Do Until Cells(i + 1, 1) = ""
If Cells(i, 1) + 1 <> Cells(i + 1, 1) Then
Rows(i + 1).Insert (xlDown)
Cells(i + 1, 1) = Cells(i, 1) + 1
Cells(i + 1, 6) = 0
For j = 2 To 5
Cells(i + 1, j) = Cells(i, 5)
Next j
End If
i = i + 1
Loop
End Sub 'この行まで

尚、一旦マクロを実行すると元に戻せませんので別Sheetにコピー＆ペーストしてマクロを試してみてください。

以上、参考になれば良いのですが・・・m(__)m

補足

ご回答ありがとうございます。
分かりやすく書いていただいたので早速に試させて頂きました。
120698, 11655, 11655, 11655, 11655, 0
120699 11655, 11655, 11655, 11655, 0
120700 11654, 11655, 11652, 11653, 12
120701 11654, 11655, 11653, 11653, 12
120702 11654, 11654, 11653, 11654, 9
120703 11654, 11654, 11654, 11654, 0
120704 11655, 11656, 11655, 11656, 2

挿入はうまくいったのですが時間の分が１００分単位になりました。(＾-＾；)
仰られるように今掲載のデータはシリアル値ではございません。
元データはシリアル値なのですがマクロで書式を少し変えて別名で保存したものです。
元データは日付と時間が別になっていまして時間部分のセルが時刻のシリアル値になっていると思います。
下記元データです。（上記とは違う時間帯）
2011.01.12, 1:28, 11670, 11671, 11671, 11670, 2
2011.01.12, 1:27, 11672, 11672, 11672, 11672, 1
2011.01.12, 1:26, 11671, 11672, 11672, 11671, 2
2011.01.12, 1:25, 11673, 11673, 11673, 11673, 1
2011.01.12, 1:24, 11672, 11672, 11673, 11672, 5
2011.01.12, 1:23, 11673, 11673, 11673, 11673, 1
2011.01.12, 1:19, 11671, 11672, 11672, 11671, 2

回答No.4

Wendy02

ご質問の、大事な最初と最後が省略されていたようです。
まだ、抜けている部分はあるのですが、中途で返事させていただきます。

私の経験では、ここのカテゴリでは初めての質問です。まったく知らないわけではありませんが、そういう方式は、RSSではありません。昔、そのようなものがあったという記憶です。逆に、今どきのソフトで、そういう方式のものがあるのですか？20年前ならともかく、10年以上前のソフトでも、Excelに出力していました。

実は、私は、同じような質問を、5年前ぐらいから専門に回答してきたのです。だから、いつも同じだと勘違いしてしまったのです。そのようなソフトウェアがあるなら、こちらの認識不足です。

今の状態では、Excelの範疇ではありません。値が出力されるイベントも、たぶんポートを監視するような方法だと思いますから、このままでは、Officeでの解決の範疇ではないはずです。新たに、ファイルの更新のチェックのイベントを作ることは出来ますが、私の経験では、Excelが不安定でしょうがないような気がします。

これだけで要点がみえている人なら、このままの話でマクロを書く人もいるかと思いますが、今の私は、そのようなものは、あまりにもみっともないコードですから、もう書けないのです。

例えば、1分毎のチェックぐらいは、VBAでも出来ますが、インポートもエクスポートも関係なくファイルを変更させることも出来ます。でも、そんなことをしたら、相手のソフトウェアとコンフリクトする可能性がないわけではありません。

せめて、利用しているソフトウェアの設定をExcelに出力設定をしていただくようにしなければ、現在のマクロを改編しても解決しません。その説明書を良く読んでいただいて、ポイントを掴んで説明していただき、その上で、設定を変えていただくことが必要かと思います。いちいち、CSVをインポートしていくというなら、まったく別のマクロです。

お礼

Wendy02さま
このたびは私の説明不足で大切なお時間を割かせてしまって誠に申し訳ございません。
もう一度私自身で把握いたしまして的確に説明できるように勉強いたします。
ご好意に感謝いたします。m(＿ ＿)m

回答No.3

Wendy02

#2様の質問を含めて、株価の取得は方法は想像の範囲ですが、おそらくRSSだと思います。ご質問者さんはご存知か分かりませんが、今どきは、OLEで、直接、プログラムでExcelにインポートしていますから、Real Time ということは、マクロをご存知の方ならお分かりの通り、何かのイベントが働くはずだと考えてよいと思います。こういう場合は、シートに細工をして、Calculate イベントでよいのかと思います。想像以上に難しいです。

'標準モジュール
Public Sub SortBlanksInput()
Dim LastRow As Long
Dim i As Long, j As Long
Dim ret, r1 As Range
Dim a As Variant, v As Variant
Const COL　As Long = 1 '何列目から？開始
Dim flg As Boolean
flg = False
LastRow = 0
LastRow = Cells(Rows.Count, COL).End(xlUp).Row
Application.ScreenUpdating = False
For i = 2 To LastRow - 1
　If Cells(i, COL).Value <> Cells(i + 1, COL).Value - 1 Then
　　For j = 1 To Cells(i + 1, COL).Value - Cells(i, COL).Value - 1
　　　Cells(Rows.Count, COL).End(xlUp).Offset(1).Value = Cells(i, COL).Value + j
　　　If flg = False Then flg = True
　　Next
　End If
Next
If flg = False Then Exit Sub
With Cells(1, COL).CurrentRegion
　.Sort Key1:=.Cells(2, 1), Order1:=xlAscending, Header:=lYes
　On Error Resume Next
　Set r1 = .Columns(5).SpecialCells(xlCellTypeConstants, 23)
　Set r2 = .SpecialCells(xlCellTypeBlanks)
　On Error GoTo 0
　If r1 Is Nothing Or r1.Areas.Count = 0 Then
　　Exit Sub
　Else
　　For i = 1 To r1.Areas.Count - 1
　　　r2.Areas(i).Resize(, 4).Value = r1.Cells(r1.Count).Value
　　　r2.Areas(i).Offset(, 4).Resize(, 1).Value = 0
　　Next
　End If
End With
Application.ScreenUpdating = True
End Sub

このマクロで、静的データでミスが出ないか、十分に試してみてください。
それがOk になったら、インポートするシートのモジュールに以下を貼りつけます。
インポートされるのは、A1をターゲットにされています。

Private Sub Worksheet_Calculate()
　 If Not Me Is ActiveSheet Then Exit Sub
　 On Error GoTo ErrHandler
　 Application.EnableEvents = False
　 Call SortBlanksInput
ErrHandler:
　 If Err.Number > 0 Then
　　 Beep　'エラー発生
　 End If
　 Application.EnableEvents = True
End Sub

そして、どこか、シートの見えない端っこでもよいので、
数式で、=NOW()
と入力して、日付を出してください。
誤動作する可能性があるので、十分に試験をしてからにしてください。

この種のマクロの質問は、英語圏で出ていますが、まともな回答は、何年も見続けていますが、ひとつもヒットしません。本来、開発言語が違うものだと思います。Windows イベントのひとつを捉えれば可能ですが、Microsoft 側の情報は、当時はよかったのですが、現在では適用されません。

参考URL：

http://support.microsoft.com/support/excel/content/onevent/onevent.asp?SD=gn&LN=ja&gssnb=1#ondata

補足

ご回答ありがとうございます。
私のやり方がまずいかもしれませんね。

最初は標準モジュールに貼り付けはできました。

＞それがOk になったら、インポートするシートのモジュールに以下を貼りつけます。
オブジェクトＳＨＥＥＴ１に貼り付けました。

＞そして、どこか、シートの見えない端っこでもよいので、
＞数式で、=NOW()
データのシートの何も無いところに書き込みました。

=NOW()を書き込みと同時にマクロが動き出しました。
時間が１００分単位になりました。
そして挿入されたデータはすべて11623と一定の数字になりました。

100529 11673 11673 11673 11673 1
100530 11623 11623 11623 11623 0
100531 11623 11623 11623 11623 0
100532 11672 11672 11672 11672 1
100533 11673 11673 11672 11672 2
100534 11671 11672 11670 11670 5
100535 11671 11671 11671 11671 1
100536 11670 11671 11669 11669 4
100537 11623 11623 11623 11623 0
100538 11670 11671 11670 11670 3
100539 11623 11623 11623 11623 0
100540 11669 11669 11667 11669 3
100541 11623 11623 11623 11623 0

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

100559 11623 11623 11623 11623 0
100560 11623 11623 11623 11623 0
100561 11623 11623 11623 11623 0
100562 11623 11623 11623 11623 0
100563 11623 11623 11623 11623 0
100564 11623 11623 11623 11623 0

私のやり方がまずいのと説明不足で申し訳ございません。 m(__)m

回答No.2

nattocurry

株のことは全然解らないので、知らない単語があって、思い違いをしているところもあるかもしれませんが、、、

1分ごとにリアルタイムでエクセルに取り込むのはどのような方法でやっているのでしょうか？

＞この抜けているところに次の足が刻む時、その抜けているところに直前の終値110130の11637を同時に挿入させたいのです。

確認ですが、あなたが、気が向いたときに、保存したファイルに対して修正(補間挿入)のマクロを実行するのではなくて、リアルタイムで自動？取り込みの際に、抜けている時間があったら、そのときに補間挿入するしたい、ということですよね？

一行に、「日＆時分」から「出来高」まで、6列のデータがありますが、どのようなタイミングで、どのような順序で記入されますか？

補足

ご回答ありがとうございます。
説明不足で申し訳ありませんでした。

＞1分ごとにリアルタイムでエクセルに取り込むのはどのような方法でやっているのでしょうか？
あるソフトのエクスポート機能でＣＳＶがデータ更新毎？に上書き保存されています。

＞確認ですが、あなたが、気が向いたときに、保存したファイルに対して修正(補間挿入)のマクロを実行するのではなくて、リアルタイムで自動？
実は保存されたデータを一度修正（tom04様に補足）し別名で５秒毎に保存しています。

そのマクロに組み込みたいと考えた次第でございます。

分かりにくくてすみません。m(__)m