ホームページ上のCSVファイルを、エクセルに自動取り込みする方法

質問

ホームページ上のCSVファイルを自動でダウンロードし、そのデータをエクセルに貼り付ける方法を知りたいのですが、ご存知の方お願いします<(_ _)>

やりたい事は、”USA　YAHOO　FINANCE”の個別銘柄の”Historical Prices”をエクセルに落とし込みたいのですが、毎日手動で”Download To Spreadsheet”を押して、落としてきたCSVファイルのデータを、データ解析用のエクセルにコピペするというのを自動化したいのです。

回答No.3

hotosys · ベストアンサー

基本的にはありえない日付でいいはずです。
QueryTablesをaddし続けてるので以前のものをDeleteするように修正しましたのがsample1です。
ただ、sample1では一度エラーを起こすと次からの読み込みができない症状が起きています。
回避方法を探していますがわからない状態です。
同様の症状が起きるようなら、QueryTablesを使わないSample2を試してみてください。

Sub sample1()
Dim url As String
Dim sDate As String
Dim eDate As String
Dim Symbol As String
Dim i As Integer
sDate = "1000/1/1" '開始日
eDate = "2010/12/31" '終了日
Symbol = "IBM" '銘柄
url = "http://ichart.finance.yahoo.com/table.csv?g=d&ignore=.csv"
url = url & "&s=" & Symbol
url = url & "&a=" & Month(sDate) - 1 & "&b=" & Day(sDate) & "&c=" & Year(sDate)
url = url & "&d=" & Month(eDate) - 1 & "&e=" & Day(eDate) & "&f=" & Year(eDate)
'データ読み込み
With ActiveSheet
.Cells.Delete
For i = 1 To .QueryTables.Count
.QueryTables(1).Delete
Next
With .QueryTables.Add(Connection:="TEXT;" & url, Destination:=.Range("A1"))
.TextFileCommaDelimiter = True
.Refresh
End With
End With
End Sub

Sub sample2()
Dim url As String
Dim sDate As String
Dim eDate As String
Dim Symbol As String
sDate = "1000/1/1" '開始日
eDate = "2010/12/31" '終了日
Symbol = "IBM" '銘柄
url = "http://ichart.finance.yahoo.com/table.csv?g=d&ignore=.csv"
url = url & "&s=" & Symbol
url = url & "&a=" & Month(sDate) - 1 & "&b=" & Day(sDate) & "&c=" & Year(sDate)
url = url & "&d=" & Month(eDate) - 1 & "&e=" & Day(eDate) & "&f=" & Year(eDate)
'データ読み込み
Dim response As String
Dim xml As Object
Set xml = CreateObject("Microsoft.XMLHTTP")
'Set xml = CreateObject("MSXML2.XMLHTTP") 上記でエラーの場合
With xml
.Open "get", url, False
.send
If (.Status >= 200) And (.Status < 300) Then
response = StrConv(.responseBody, vbUnicode)
Else
MsgBox "読み込みエラー:(" & .Status & ")" & .statustext
End If
End With
Set xml = Nothing
'シートに書き込み
Dim i As Long
Dim rowData() As String
Dim columnData() As String
rowData = Split(response, vbLf)
With ActiveSheet
.Cells.Delete
For i = 0 To UBound(rowData)
columnData = Split(rowData(i), ",")
If UBound(columnData) >= 0 Then
.Range(.Cells(i + 1, 1), .Cells(i + 1, UBound(columnData) + 1)) = columnData
End If
Next
End With
End Sub

お礼

ありがとうございます！
ありえない日付で問題ありませんでした。
sample1、sample2どちらも問題なく動作しました。
手作業より数十倍作業が捗ります。
感謝です<(_ _)>

補足

すみません、お礼を言った後にまたまた質問が、、、、^^;

教えていただいたsample2でデータを取り、他のエクセルにそのデータをコピペし、そのデータを元に関数を組んでいるのですが、関数の解が#DIV/0!となり、どうも入力されていないといった認識を、エクセルはしているように見受けられます。

コピペするとき”形式を選択しての貼り付け”で色々と試したり、セルの書式設定をいじったりとしたのですが、データとして認識されませんでした。

どのようの方法であればデータ認識されるのでしょうか？？？

回答No.4

hotosys

手動またはsample1で取得した場合は大丈夫でしょうか？
sample2のみダメな場合は、データを比較してみてください。

お礼

手動またはsample1で取得した場合は大丈夫でした。
sample1でマクロ構築しました。

色々と回答頂きまして、ありがとうございました。
とても助かりました<(_ _)>

回答No.2

hotosys

例えば
http://finance.yahoo.com/q/hp?s=IBM
で[SET DATE RANGE]の日付を入力して[Download To Spreadsheet]するデータを
[データ][外部データの取り込み][テキストファイルのインポート]で現在のシートに取り込む場合なら以下ではどうでしょうか?
Sub sample()
Dim url As String
Dim sDate As String
Dim eDate As String
Dim Symbol As String
'パラメータ作成（月は-1の値を要求するみたい)
sDate = "2008/8/11" '開始日
eDate = "2008/8/20" '終了日
Symbol = "IBM" '銘柄
url = "http://ichart.finance.yahoo.com/table.csv?g=d&ignore=.csv"
url = url & "&s=" & Symbol
url = url & "&a=" & Month(sDate) - 1 & "&b=" & Day(sDate) & "&c=" & Year(sDate)
url = url & "&d=" & Month(eDate) - 1 & "&e=" & Day(eDate) & "&f=" & Year(eDate)
'データ読み込み
With ActiveSheet
.Cells.Delete
With .QueryTables.Add(Connection:="TEXT;" & url, Destination:=.Range("A1"))
.TextFileCommaDelimiter = True
.Refresh
End With
'.Cells.Copy Destination:=Sheets("Sheet2").Range("A1") 'ここらで他のシートにコピーする
End With
End Sub

補足

ありがとうございます！！
うまく取り込めました！！

後もう一点だけお知恵を貸して下さい<(_ _)>

sDataをその銘柄の最長(MAX)となる日付に、eDateをVBAを走らせる日に自動で設定するためには、どのようにVBAを書けばうまく出来るのでしょうか？

IBMであれば、1962/1/2から今日までのデータが最長
DELLだと1988/8/17から今日までが最長

手動でCSVを落としてくる場合は、1950/1/1と適当にかなり昔の日付を入れると最長が取れるので、試しにVBAの式にも固定値をはめてみましたがエラーとなってしまいました。

やはりスピンボタンを使って、手動で設定するしかないのでしょうか？

マクロを走らせたら、今日から最長のデータを取得する、完全自動化になるのを目指しております。

回答No.1

pulsa

質問事項が複数あるようですので、一つに絞って質問した方が良いように思います

”USA　YAHOO　FINANCE”の個別銘柄を取り込んだ事がないのでわかりませんが、ページ上にボタンがあってそのボタンを押すと、CSVファイルがどこかに保存されるのでしょうか？
そうであれば、その保存されたファイルをエクセルのマクロで自動的に取り込む動作となります
ページ上のボタンを自動的に押す動作は、エクセルからIEを制御することになります
エクセルからIEを操作する方法は幾つかありますが、もっとも簡単なのはSendKeyで、キーボードの操作をエクセルから行う方法があります
ただし、この方法はIEの表示を確認する必要性などから、実装は簡単ですが、制御が難しいと言う欠点があります
他には、IEをObjectとして制御する方法がありますが、結構な歯ごたえなので、ここのみで説明するのが難しいので、WEBを参考にしてください
http://www.ken3.org/cgi-bin/group/vba_ie.asp

どこかに保存されたCSVファイルの取り込みも幾つか方法があります
これも簡単な方法から、ちょっとメンドクサイ方法までさまざまですが、一般的な方法としては、Open ステートメントを使ってLineinputなどで取り込みます
エクセルのヘルプにある動作なので、こちらもWEBにたくさん方法が載っています
http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_110_020.html

変わった所だと、Workbooks.Open を使ってCSVファイルを開いて（自動的にエクセルで開かれる）、開いたエクセルからシートをコピーする
なんて方法もあります

お礼

エクセルからのIE制御という方法もあるのですね。
HPの紹介ありがとうございます。