download .csv file indirectly from website - long

[not2brite]

I'm trying to create a spreadsheet that when I click a button it wil
download the .csv file directly to my harddrive then I can use anothe
routine to open it after it's downloaded(or better yet open/downloa
directly) into another sheet in the workbook. My problem is I'm no
sure if, for the given site, I need to learn xml or javascript. I'v
seen many references to xml on the forums but the site appears to us
java script.

Here is a piece of code I was able to make work by reading the post
here and a book called "Excel 2000 Power Programming"

<code>
----------------------------------
Function GetDataButton()

Dim DataHere As Range
Dim msn As QueryTable
Dim address As String

Set DataHere = Worksheets("Sheet1").Range("$F1")

address = "URL;http://tinyurl.com/2r7l6"

Sheets("Sheet1").Select
Set msn = ActiveSheet.QueryTables.Add(Connection:=address
Destination:=DataHere)
msn.Refresh

End Function
----------------------------------
</code>

Please note this code DOES NOT do what I want(and the ticker symbol i
hardcoded into it) but it does link to the page where I then have th
option to save it.
My problem is I can't see for the life of me a url to the .csv file
Here's a snip:

<code>
----------------------------
<SCRIPT LANGUAGE=javascript>
<!--//
document.QlistCtl.Symbol.focus();document.QlistCtl.Symbol.select();
//-->
</SCRIPT>
</TR><TR><TD VALIGN=TOP><TABLE WIDTH=100% BORDER=0 CELLSPACING=
CELLPADDING=0><TR><TD VALIGN=TOP>
<TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0 ALIGN=LEFT WIDTH=400>

<FORM NAME=Charts ACTION=chartdl.asp METHOD=GET>
<TR>
<TD ALIGN=RIGHT WIDTH=307>
<INPUT TYPE=IMAGE name="FileDownLoadBt" SRC="images/downlbut.gif
border=0 WIDTH=90 HEIGHT=20>
</TD>
<TD WIDTH=8><SPACER TYPE=BLOCK WIDTH=8></TD>
<TD ALIGN=RIGHT WIDTH=90>
<INPUT TYPE=IMAGE name="ShowChartBt" SRC="images/showchrt.gif
border=0 WIDTH=90 HEIGHT=20>
<INPUT TYPE=HIDDEN NAME="Symbol" VALUE="mrk">

</TD>
</TR>
</FORM>
</TABLE>
<BR CLEAR=ALL>
<B>Merck & Co Inc</B>
<BR CLEAR=ALL>

<!-- Begin stock History table -->
--------------------------------
</code>

Is this line -
document.QlistCtl.Symbol.focus();document.QlistCtl.Symbol.select();
telling the page/browser what to do if either the download file butto
or show chart button is pressed? If it is then it appears I'm going t
need to get a grasp of javascript to be able to see what it's doing
Once I know that, is that where the XML stuff comes in? Basicall
trying to send the page/server/whatever info as if it came from th
webpage and the page "responds" accordingly? I glossed over a book a
the store on xml but I didn't see any refernce to working within vb
code but I could have easily missed it.

It's been 6-7 years since I've messed with html(a passing interest).
never bothered to learn vbscript or javascript. I've been using exce
for a few months to run numbers on stocks and basically cut/paste th
info I want but that's getting old. I've written some simple subs an
functions up to this point is all. I just started this project Tuesda
so I've still got alot more learning to do but that's where the fu
is.

Thanks for any input,
Bra

 

[Tom Ogilvy]

A simpler approach might be to do a web query:

Sub AAA_URL_Get_Query()
Dim sStr As String
sStr = "URL;http://moneycentral.msn.com/investor/charts/" & _
"chartdl.asp?Symbol=mrk&DateRangeForm=1&PT=5&CP=1" & _
"&C5=1&C6=1974&C7=1&C8=2004&C9=2&ComparisonsForm=1" & _
"&CE=0&CompSyms=&DisplayForm=1&D0=1&D5=0&D7=&D6=&D3=0" & _
"&ShowTablBt=Show+Tablec"

With ActiveSheet.QueryTables.Add(Connection:= _
sStr, _
Destination:=Range("a1"))

.BackgroundQuery = False
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub



This brings down the whole page, but you could add code to find the table
and copy it to a new worksheet and delete the old worksheet.

You could also make the symbol value a variable when creating the string.

 

[keepITcool]

This will just download the .csv file

http://data.moneycentral.msn.com/scripts/chrtsrv.dll?
Symbol=mrk&FileDownload=&



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

A simpler approach might be to do a web query:

Sub AAA_URL_Get_Query()
Dim sStr As String
sStr = "URL;http://moneycentral.msn.com/investor/charts/" & _
"chartdl.asp?Symbol=mrk&DateRangeForm=1&PT=5&CP=1" & _
"&C5=1&C6=1974&C7=1&C8=2004&C9=2&ComparisonsForm=1" & _
"&CE=0&CompSyms=&DisplayForm=1&D0=1&D5=0&D7=&D6=&D3=0" & _
"&ShowTablBt=Show+Tablec"

With ActiveSheet.QueryTables.Add(Connection:= _
sStr, _
Destination:=Range("a1"))

.BackgroundQuery = False
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub



This brings down the whole page, but you could add code to find the table
and copy it to a new worksheet and delete the old worksheet.

You could also make the symbol value a variable when creating the

string.

 

[Tom Ogilvy]

That is certainly simpler, but doesn't seem to return the same data. Do you
have a source where you got it that perhaps show any arguments that may be
passed?

 

[keepITcool]

Hi Tom..

it's exactly the link produced when you click on the download button..
(at least it's what i copied from my IE history after i downloaded the
file)

since OP was interested in the csv... i did a quick check,
appears csv data is not linked to the onscreen table...
csv is ALWAYS last 12 months' stats




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[not2brite]

Bum deal, oh well.
The data I want is monthly prices(high,low,close,volume), that ur
seems to get daily for the past year.

If this is the only way then that's fine. It works and will do what
need.

A dumb question, so that I can run this routine multiple times withou
having to save and re-open each time(not all companys have same tabl
lengths),since it needs a clean slate each time for my other routine
to run properly(they find the end of the table). Could I "create" th
sheet it downloads to and have it delete the sheet after everythin
else runs? This way I start with a clean slate each time(I'm hoping t
keep all the data on one one sheet and link to it as needed if tha
makes sense.
From looking at all the things you can do(and Tom's comments) I'm sur
I can do it but figured I'd double check. The nice thing is if it'
done right(create new sheet, find table in sheet, etc), even if the
change the way the page is formatted I hopefully won't have to re-writ
my code.

Tom,
May I ask why you suggested changing the code? Isn't my code a we
query as well just written differently? Or would my code be considere
a hyperlink of some type?

Thanks for such quick replies,
Bra

 

[not2brite]

OK wait I'm confused. How can the .csv only be past 12 months when
can hit the download file button from my link
http://moneycentral.msn.com/investo...&D4=1&D5=0&D7=&D6=&D3=0&ShowTablBt=Show+Table
and it lets me save the data I want as a .csv file?
Or do you mean from the /scripts directory it's only linked to the pas
12 months.

Thanks,
Bra

 

[not2brite]

Here's this url
URL;http://tinyurl.com/35kfk

It seems to get the data I want but when I just paste it into m
code(didn't try Tom's) it uses only one column to paste all the data
That will work, just needs to be parsed. It's getting closer since it'
the data I need and it's only the table without all the rest of th
webpage crap.
Now I need to figure out what the other parameters/arguments in the ur
are. I know C5 & C7 are the months. After work I'll figure the rest ou
that way hopefully I'll be able to enter what I want in the spreadshee
just as if I went to the site and did it.

Now for a really dumb question, why when I run my code multiple time
does it shift the previous data right? If I hit my "GetDataButton"
times(wait until it runs before rehitting) I end up with four column
of data startings at F1. Shouldn't it just paste over top of it since
specified a range?

Thanks again,
Bra