Download a .csv file from a website

I was wondering if anyone knew how to code this with vba. I have a list of 
stocks in a spreadsheet, and want to download the .csv file from Yahoo 
historical quotes for a stock. The code would have to navigate to 
http://finance.yahoo.com/q/hp?s=A, navigate to the "Download to Spreadsheet" 
button, simulate a right click and save as, and save the .csv file as the 
nameofthestock.csv, in this case A.csv, and save it to the directory where I 
want it. Is this possible? I've been searching online and in vba reference 
books for a few days, and can't find any code like this. 

The reason I'm not attaching any code is because I can't even find a 
starting point to do this. Any help would be great.
0
Utf
12/30/2009 6:46:01 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
2974 Views

Similar Articles

[PageSpeed] 25

There are several ways of going about this, but i prefer simple. If you right 
click the download button and click properties, you will see that you now 
have a url to the csv download which is handy (and you can still plug in the 
stock name you want).

Now for the download, XMLHTTP is a cool object that lets you work with teh 
web, the following code was borrowed and changed from the following:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=799

I went ahead and did the whole thing because it was kind of fun, you can 
play with it to learn from since you seemed to be looking to do it on your 
own. You just need a button to run this one.

Private Sub CommandButton1_Click()
Dim sfileName As String
Dim sStockName As String
Dim sUrl As String
sStock = "MSFT"
sUrl = "http://ichart.finance.yahoo.com/table.csv?s=" & sStock & 
"&d=11&e=30&f=2009&g=d&a=10&b=18&c=1999&ignore=.csv"
sfileName = "C:\Documents and Settings\john.bundy\Desktop\" & sStock & ".csv"
SaveWebFile sUrl, sfileName
End Sub
Function SaveWebFile(ByVal vWebFile As String, ByVal vLocalFile As String) 
As Boolean
    Dim oXMLHTTP As Object, i As Long, vFF As Long, oResp() As Byte
     
     'You can also set a ref. to Microsoft XML, and Dim oXMLHTTP as 
MSXML2.XMLHTTP
    Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP")
    oXMLHTTP.Open "GET", vWebFile, False 'Open socket to get the website
    oXMLHTTP.Send 'send request
     
     'Wait for request to finish
    Do While oXMLHTTP.ReadyState <> 4
        DoEvents
    Loop
     
    oResp = oXMLHTTP.responseBody 'Returns the results as a byte array
     
     'Create local file and save results to it
    vFF = FreeFile
    If Dir(vLocalFile) <> "" Then Kill vLocalFile
    Open vLocalFile For Binary As #vFF
    Put #vFF, , oResp
    Close #vFF
     
     'Clear memory
    Set oXMLHTTP = Nothing
End Function



-- 
-John http://www.jmbundy.blogspot.com/
Please rate when your question is answered to help us and others know what 
is helpful. 


"talkintotim" wrote:

> I was wondering if anyone knew how to code this with vba. I have a list of 
> stocks in a spreadsheet, and want to download the .csv file from Yahoo 
> historical quotes for a stock. The code would have to navigate to 
> http://finance.yahoo.com/q/hp?s=A, navigate to the "Download to Spreadsheet" 
> button, simulate a right click and save as, and save the .csv file as the 
> nameofthestock.csv, in this case A.csv, and save it to the directory where I 
> want it. Is this possible? I've been searching online and in vba reference 
> books for a few days, and can't find any code like this. 
> 
> The reason I'm not attaching any code is because I can't even find a 
> starting point to do this. Any help would be great.
0
Utf
12/30/2009 8:48:01 PM
Thanks, this is great. My one question is since the url for the csv is 
programmed by the website for a specific date range, is there anyway to have 
the date automatically update? So next week if I wanted to look at the 
historical data of the stock, I could just click the button without 
reprogramming in the url for the csv file.

Thanks again for this info!

"John Bundy" wrote:

> There are several ways of going about this, but i prefer simple. If you right 
> click the download button and click properties, you will see that you now 
> have a url to the csv download which is handy (and you can still plug in the 
> stock name you want).
> 
> Now for the download, XMLHTTP is a cool object that lets you work with teh 
> web, the following code was borrowed and changed from the following:
> http://www.vbaexpress.com/kb/getarticle.php?kb_id=799
> 
> I went ahead and did the whole thing because it was kind of fun, you can 
> play with it to learn from since you seemed to be looking to do it on your 
> own. You just need a button to run this one.
> 
> Private Sub CommandButton1_Click()
> Dim sfileName As String
> Dim sStockName As String
> Dim sUrl As String
> sStock = "MSFT"
> sUrl = "http://ichart.finance.yahoo.com/table.csv?s=" & sStock & 
> "&d=11&e=30&f=2009&g=d&a=10&b=18&c=1999&ignore=.csv"
> sfileName = "C:\Documents and Settings\john.bundy\Desktop\" & sStock & ".csv"
> SaveWebFile sUrl, sfileName
> End Sub
> Function SaveWebFile(ByVal vWebFile As String, ByVal vLocalFile As String) 
> As Boolean
>     Dim oXMLHTTP As Object, i As Long, vFF As Long, oResp() As Byte
>      
>      'You can also set a ref. to Microsoft XML, and Dim oXMLHTTP as 
> MSXML2.XMLHTTP
>     Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP")
>     oXMLHTTP.Open "GET", vWebFile, False 'Open socket to get the website
>     oXMLHTTP.Send 'send request
>      
>      'Wait for request to finish
>     Do While oXMLHTTP.ReadyState <> 4
>         DoEvents
>     Loop
>      
>     oResp = oXMLHTTP.responseBody 'Returns the results as a byte array
>      
>      'Create local file and save results to it
>     vFF = FreeFile
>     If Dir(vLocalFile) <> "" Then Kill vLocalFile
>     Open vLocalFile For Binary As #vFF
>     Put #vFF, , oResp
>     Close #vFF
>      
>      'Clear memory
>     Set oXMLHTTP = Nothing
> End Function
> 
> 
> 
> -- 
> -John http://www.jmbundy.blogspot.com/
> Please rate when your question is answered to help us and others know what 
> is helpful. 
> 
> 
> "talkintotim" wrote:
> 
> > I was wondering if anyone knew how to code this with vba. I have a list of 
> > stocks in a spreadsheet, and want to download the .csv file from Yahoo 
> > historical quotes for a stock. The code would have to navigate to 
> > http://finance.yahoo.com/q/hp?s=A, navigate to the "Download to Spreadsheet" 
> > button, simulate a right click and save as, and save the .csv file as the 
> > nameofthestock.csv, in this case A.csv, and save it to the directory where I 
> > want it. Is this possible? I've been searching online and in vba reference 
> > books for a few days, and can't find any code like this. 
> > 
> > The reason I'm not attaching any code is because I can't even find a 
> > starting point to do this. Any help would be great.
0
Utf
12/30/2009 10:30:01 PM
Reply:

Similar Artilces:

upgrading to latest service pack of 9.0, file not found error
Good morning! I'm working with a customer who is upgrading to the latest service pack of GP 9.0 (they're not ready to move to 10). We installed 9.0, then installed the latest service pack of 9.0 and receive an error when launching GP Utilities: "File not found: D:\program files\microsoft Dynamics\GP\SQL\0\Upgrade.dll.UPGRADE_9__259.DRIVER_INI" I thought the fix was to create a file in that path, but doesn't seem to be working. Any other thoughts on how to fix this? -- --Peter peter, what version of gp 9 you installed, language and what version of service pack ...

Opening/linking .xlsx files
I have had trouble linking new .xlsx files to my existing .xls files. Excel would shut down. So I started saving all my files in the old .xls format. I recently created a new file using .xlsx, but when I try to reopen it, Excel shuts down. Any suggestions??? -- Jim Are you able to open Excel 2007? Try to create a new file and reopen it. If this does not work then try... Close Excel first and On the Windows Taskbar 1) Start->Run-> excel.exe /unregserver ->OK. 2) Start->Run-> excel.exe /regserver ->OK. See the space between exe and /regserver You might have to design...

List of files
All I want to do is get a list of files in a directory so they can be deleted. How do I get this list ? John, > All I want to do is get a list of files in a directory so > they can be deleted. How do I get this list ? You can use FindFirstFile/ FindNextFile/FindClose API functions for enumerating files. If you want to remove directory with its content take a look at SHFileOperation in MSDN. -- Regards, Kobi Ben Tzvi "John" <john.f.brownell@wdc.com> wrote in message news:022b01c3788a$bb119ee0$a301280a@phx.gbl... > All I want to do is get a list of files in...

Duplicate Transactions from Automatic Downloads
Hello - About a month ago, transactions that Money 2007 automatically download from Schwab, started getting duplicated. Each time accounts were updated, I would get all transactions that happened within the last couple of days, regardless of whether they were downloaded before. Is this likely an issue with a corrupt MS Money file, or a problem with the download service? Is anyone else experiencing this, and does anybody know a solution? Thanks, Reddog Hey Reddog, Have a look at the thread started 11/15/07 titled 'Duplicate Transactions Downloading'. Rapid Robert "Re...

Transfer Files Using VBA
Hi, I'd like to transfer files from several drives on my pc to one cetnral location. From A:\Office1\ B:\Office2\ C:\Office3\ To D:\HeadQuarters\ My routine now is the following 1) Copy the files from drives: A, B, and C; and then 2) Paste the filesto the D drive 3) Delete the files permanently from drives A, B, and C. As you see this is a tedious routine, which I'd liket o avoid by using VBA. Can someone start me off with some code on how to accomplish this. Thanks so much in advance! Jrew Jrew, You could create a batch file (from the olden days) using XCOPY and desired bel...

Importing QFX files
One of the financial institutions that I deal with provides downloads only in QFX format, not OFX. Is there anyway to import QFX into Money 2005 Standard? John Kane On 2005-04-29, jkane@ida.org <jkane@ida.org> wrote: > One of the financial institutions that I deal with provides > downloads only in QFX format, not OFX. Is there anyway to import > QFX into Money 2005 Standard? I don't know the answer to this question, but this guy seems to think that it doesn't really matter. According to him, QFX is OFX with Quicken extensions. I don't know whether Money will ...

Windows Live mail not downloaded into Outlook
I have an msn account, Outlook 2007, and Outlook connector installed on a Vista machine. Recently I've had problems downloading mail to Outlook. Outlook Connector shows that Calendar and Contacts are connected, but that there is an error in connecting mail. The error message says: Error in Mail Error with Send/Receive. Look for a log in the Sync Issues folder. Not sure where the Sync issues folder is. This has occurred at least three times in the past month. Once, I deleted my msn profile and started over, but I can't figure out why it occurred or how I was able ...

Do I have to put my files on a server to hyperlink in Publisher?
For a class project, we need to make a website. However I do not want to go live with the site (publish the site) for security purposes. Is there a way I can just make the hyperlinks for my files open without having to be on a server? The instructor wants a working but not live site. TJ If you are using Pub 2003, the built-in navigation bar uses relative links that will work on your local machine. Under Help type "navigation" for more information. Assuming you use the default setting, when you Publish to the Web, you can direct the html output to a folder on your computer. T...

Clearing Log Files
What is the method for clearing exchange log files. These file take up more space than the database. Thanks Bill Bill, I suspect the files you are talking about are the Transaction Log files. These are important files and need to be managed correctly. Performing an online backup will truncate these logs. Or if you don't require backups then turn on circular logging. Here's some more info.. http://support.microsoft.com/default.aspx?scid=kb;en-us;147524&Product=exchange Peter O'Dowd http://www.blade.net.nz "Bill" <anonymous@discussions.microsoft.com> wr...

How do I get focus back on an Excel file?
When automating Excel, if there is a picture (graphic) on the worksheet, when the file is opened, the picture has the focus and mouse-clicks anywhere on the Excel window are lost. Is there a way to get the focus back so that other cells can be editted? Insert this line of code into the macro: ActiveSheet.Range("A1").Select You could also rework the code to avoid selecting the shape in the first place. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Stumpiana" <Stumpiana...

Excel CSV leaving out empty columns from row 17 onwards
Excel omitting commas in random ways !! Anyone come across this ? When I save this file in csv using excel 2003 A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA A,,C,D,,21-Nov-06,27-Nov-06,S,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,S,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,B,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,B,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,B,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,B,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,B,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-...

How do I access Outlook Express? Where to download?
How do I access Outlook Express? Where to download This is the Outlook newsgroup. Outlook Express is a different program. You don't download Outlook Express. It's part of Internet Explorer and your O/S. Look in the Start Menu in Programs. "Frank" <Frank@discussions.microsoft.com> wrote in message news:5C27B0DE-4C72-41D3-BC87-B4652F45E4F2@microsoft.com... | How do I access Outlook Express? Where to download Frank <Frank@discussions.microsoft.com> wrote: > How do I access Outlook Express? Where to download Outlook Express probably came with your Wi...

'Recently used file' list.
When I open Excel nine filenames appear on the RHS as 'recently used files' (this number was set via Tools/Options/General, of course). In the middle of this list are 3 no-longer-used files. Though the files themselves have been deleted their names remain in this list. Is there an easy way to remove those entries from that list, please? TIA, DB. There is an entry in the help file "Display or hide recently used workbooks on the File menu" which says uncheck the 'recently used files' checkbox, the click OK. Then, basically, start again. "DB.&...

Long File Path
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hello, <br><br>When I try to save my Excel worksheet files somewhere on the HD, Excel comes back with an error message saying &quot;The file path you entered is too long&quot;. I do not want Excel to tell where to save my files! Anybody can help? <br><br>Thanks! <br> Ali Excel is most likely telling you nothing of the sort -- OS X is. Make sure you have Office & OS X fully updated then run Disk Utility - Repair Disk Permissions. If you continue to have a problem you...

XLSX Files Will Not Open
Using Windows 7 / Excel 2007. I can open old xls files using Excel 2007 but can not open any xlsx files. When I select an xlsx file to open, a new blank worksheet opens instead of the selected file. The "ignore other applications" box is unchecked as recommended in other posts and I have re-installed Office. Neither solved the problem. Please help. Kind regards, -- Windog ...

Help, how do I get an exe file with outlook 2002.
is there a way to turn off the GD stupid F#@$ing level one interecept "feature" in outlook? I am so angry with this stupid program@! I need to download an exe file and cannot with this software! Maybe if you washed your mouth out with soap, someone would be willing to help you. This is a technical forum -- not a place for ranting fools. "Sean" <sean@pointblankinc.com> wrote in message news:5a8001c42d8f$52c08b20$a401280a@phx.gbl... > is there a way to turn off the GD stupid F#@$ing level > one interecept "feature" in outlook? > > I am so angr...

Can I move the data files?
I just installed Money 2005. I let it take most of the defaults, It choose to store the data files in \My Documents. I would like to move all of them to \My Documents\My Money. Is there a way to do that? -- Simplest way is to use My Computer or Windows Explorer. Navigate to your ..mny file, click on it, click on edit > copy, navigate to where you want the ..mny file to live and click on edit > paste. When the file is in it's new home, double click on the file name. This will call up Money and this will then remember where it found the file. Now go back to the original locati...

File Conversion Question
backup.pst Outlook 2003 version Can this be converted to be restored into Outlook 2002? What other possible solutions could be employed? Thank you. denise.ciaralli@bell.ca <anonymous@discussions.microsoft.com> wrote: > backup.pst Outlook 2003 version > > Can this be converted to be restored into Outlook 2002? Only by using Outlook 2003 to create a 2002-format PST and then copying or exporting the data to the 2002-format PST. -- Brian Tillman Thank you! >-----Original Message----- >denise.ciaralli@bell.ca <anonymous@discussions.microsoft.com> wrote: > >...

How do I reduce the size of an exel file?
I am currently up to 80mb and am using Excel 2002. The file has multiple users with pivot tables. It would be a huge endeavor to reset all the pivot tables. Is there any other trick that will help reduce the size of the file? eselgar wrote: > I am currently up to 80mb and am using Excel 2002. The file has multiple > users with pivot tables. It would be a huge endeavor to reset all the pivot > tables. Is there any other trick that will help reduce the size of the file? What does the file contain and what do you mean by "multiple users with pivot tables"? -- Inter...

File Hiearchy.Any Tips?
Links etc? I have taken possession of a new XP machine and things seem a little different from my last XP that crashed and burned. I am trying to organise my file hierarchy with something like 500,000 images in say 1000 folders. I have them in My Documents, but oft times windows explorer ends up in a mirror folder tree under C:\ drive It feels awkward and troubling, any ideas how to set up a stable file tree in xp please? Should I move these files to the root drive C:\ only? Thanks very much in advance. rodney wrote: > Links etc? > > I have taken possession...

Spreadsheet File Size
I have a Spreadsheet that has 10 tabs at the bottom. Each of thes sheets cross reference dataand thee are many complex calculation throughout. Many of the formulas are nested IF functions which I kno take up a lot of space. The unfortunate thing is the file size is 7 MB, which is hard for others in the office to work with. I would appreciate any ideas as to how I could reduce the file size. For example: - Is there a way or someone who could look at this and kind of audi it to see if there are ways to reduce size? - Should I break it up into seperate files (shich would be harder fo other ...

Translation: .123 file extension to Excel 2003?
I received a file with this extension - more familiar with.wk_, extensions- can't translate to Excel. Please advise me - Thanks ...

Exchange no longer available
Courtesy of Katrina, my brother-in-law's job and Exchange server are no more. He knows he has personal folders and contacts on his laptop, but Outlook choles on not connecting to Exchange and won't let him see the local stuff either. How does one tell Outlook not to try to connect to Exchange and instead use the local pst file? It might be easier to create a new profile and then use File Open to access the personal folder file, you create a new profile in Control Panel || Mail <philmcin@yahoo.com> wrote in message news:1126736961.961262.92120@g43g2000cwa.googlegroups.com... ...

can not open lib file
hi, if a try to compile a porgramm i get always the rror : LINK : fatal error LNK1104: Datei "TranslationController.lib" kann nicht geoeffnet werden --> can not open the lib ....waht do i hier wrong please....??? thnaks You need to locate the .lib file, and make sure msdev is looking for it in the right place by looking in Tools->Options + Directories tab. Jase "Marc" <rote_granit@yahoo.com> wrote in message news:013901c3c807$5a8f8fb0$a301280a@phx.gbl... > hi, > if a try to compile a porgramm i get always > the rror : LINK : fatal error LNK1104:...

Trying to get a type of video file format to open and run on my we
I created a video using Windows Movie maker and saved as .wmp file. I saved it to my computer and then inserted on my web page under Front Page 2003. Nothing! I then converted the file to avi format - still nothing! I then tried mpeg4 still nothing. The manual is next to useless on this subject. Anyone tell me what I need to do so that when a user opens up the page the video plays automatically. -- Simon Holloway Save as a .wmv and insert a suitable player into the page. For IE only, Insert->Web Component->Advanced Controls->ActiveX Control Click Next Choose Window...