yahoo finance into excel macro

[losmi8]

hey guys, so i am trying to import

that type of information into excel via macro. i can do it by importing it via webquery but the problem is that i need more than one page (go all the way down and there is a NEXT button to get information that is older)

so i am trying to import not just one page, but lets say import 5 or 6 (which will give me a years data on a stock vs. the 3 months)

and then down the road, id like to get rid of the line that shows the dividend payout, but that isn't too important right now.

is this possible what i am asking?

[jrdnoland]

Have a look at this.

[Chippy]

Page 1 - http://finance.yahoo.com/q/hp?s=MSFT...9&g=d&z=66&y=0

Page 2 - http://finance.yahoo.com/q/hp?s=MSFT...&g=d&z=66&y=66

Page 3 - http://finance.yahoo.com/q/hp?s=MSFT...g=d&z=66&y=132

Can you see the pattern? a, b, c is the start date (month zero-based); d, e, f is the end date; g=d probably means 'Daily'; z is the number of rows-1; y the offset.

Get the first page whilst the macro recorder is running, then modify the VBA code to construct the URL with the required parameters for each page, or just use a separate web query for each page.

[losmi8]

ok, here is the better way to import them. the following link imports all the data yahoo has on a particular site:

http://ichart.finance.yahoo.com/tabl...93&ignore=.csv

that is for symbol SPY. as you can see d=month, e=day, f=year....a=month, b=day, c=year.

now, is it possible for me to make an input boxes in excel, for instance:

Stock ( ), Date needed FROM ... TO.

and whatever I type in there, the excel would then "change" that link up there with the appropriate numbers after each letter and then import the data on the sheet?

I know I am asking for a lot, but I can't seem to get this to work. I can't record a macro to do this and this code seems like it would be very difficult and I'm just a rookie at VBA.

if anyone can help, please do!

[losmi8]

i can't record a macro because i'm not importing a table this time...i have to click on a file that prompts me to either download or open the .csv file.


i just need a code that will import the link above into excel. just as the way it is. i will worry about fancy stuff later.

can anyone help me?

[losmi8]

guys, i made SOME progress. not a lot, but at least i am on the right track. here is what i have so far:

Please Login or Register  to view this content.

..this will take the file from the yahoo website and download it to my C drive.

1 problem though!

1) i dont need to download the file, i need to import it into excel right away.

how can i instead just make the code import the file vs. saving it. that is all i need for now

thank you all!

[JeanRage]

Hi,

Add whatever symbols in column A and hit GO for the macro to retrieve data from yahoo finance ...

HTH

[losmi8]

Jean, thank you for that. But I am looking for something a bit different. Read my post right above yours. I am trying to download and open the .csv file (from the website) in excel.

I have managed to save it to my C drive, at which point I guess I could record a macro and import it into excel. But I'd like to simplify the code and open it in excel automatically vs. saving it to C Drive.

[jrdnoland]

I couldn't find any import commands with that object. You could save it to a temp file and import it and then optionally delete the file. Not ideal but it would work.

Recorded this with the macro recorder:

Please Login or Register  to view this content.

[losmi8]

Thank you. But I am trying to avoid saving it to C drive at all. I know I can do it with a macro, but I will be downloading 50 stocks per day...It would be a pain in the butt to go delete them all after.


Does anyone know how to open it in directly into Excel (without saving it the hard drive).

Thank you!!

[losmi8]

anyone? im really trying to avoid downloading of a file.

[jrdnoland]

I'm sure you know that you can delete a file from code. So you wouldn't have to do anything manually just run the code, another fraction of a second?

[losmi8]

I can delete it manually, but I will be pulling 50+ stocks each day, and I wouldn't want to go in and delete the files right after each day.

It would just make my life easier and code much better if I could open it in excel right away instead of saving it.

Here is the code I have so far, it doesn't work as it gets stuck on

Please Login or Register  to view this content.

And tells me the variable is not defined? Without the textbox1 the code works just fine if I for instance define "symbol" as (symbol = "msft") ..then it will work, but if I try to type the symbol into a textbox and have the code run that symbol, then it wont work. So frustrating..

Please Login or Register  to view this content.

[jrdnoland]

A couple of things:
1. You're declaring symbol in your keydown event; sub stock won't know it exists. Either make it a global variable or declare it inside the getStock sub

2. Option Explicit requires you to declare all variables, you didn't declare oStream. Either declare it or get rid of Option Explicit.

Please Login or Register  to view this content.

[losmi8]

oh wow! that does work, thank you for taking the time to explain it all as well!

only problem i have with the code right now is that it is saving the file to my C: instead of just opening it in directly into excel.

and i have to change the code always as the code can't "re-write" the file. so i have to keep changing where it has to save it. it is annoying and kills the purpose of the code, as i could goto yahoo and manually download the file easier/faster.

is there any way the code can open directly into a predetermined range in excel, say starting at call G1 and to the right and down?

[jrdnoland]

Combine the code below with the code I recorded from the macro recorder.

I'm not sure if you can import a file without saving it?

But you can 1. Save it 2. Import it 3. Delete it

Use the Function to make sure it's still there and then the sub to delete it. You can then save it again and repeat.

Please Login or Register  to view this content.

Note: FileToDelete and FileToTest are strings; you'll want to use the complete path and filename. e.g., "C:\Subdirectory\Filename.ext"