Yahoo Finance to Excel Help

[Liosis]

Hello All,

First and foremost, I have very little VBA Macro experience and have a couple items I'm attempting to do that I'm hoping someone can help me out with. I currently have an excel spreadsheet that I constructed that does all sorts of stock analysis perfectly once I get the historical stock information imported to the first tab ("Input" tab)...my problem is that importing the individual stock data into the spreadsheet is a manual process right now that I'd like to automate (it will save me so much time).

So here is my current process:

1. I go to Yahoo Finance and type in the stock I'm looking for...we'll use "CL" for this example
2. Once I'm at the CL summary page, I navigate to the "Historical Prices" page via the left side menu
3. Once I'm at the "Historical Prices" page I scroll down to the bottom of the table where it says "Download to Spreadsheet" and right-click it and choose "Save Linked File to Desktop"
(This is the webpage version of the excel prices download: http://ichart.finance.yahoo.com/tabl...=d&ignore=.csv )
4. I then open the saved excel file, select all of the information, and copy and paste into the "Input" tab of my spreadsheet at cell A1
5. At this point, the yahoo page is still open and I select "Dividends Only" from the "Historical Prices" page options, click on "Get Prices", and then repeat steps 3 and 4 for the new dividend table with the only exception being that the information is pasted into cell I1 of the "Input" tab on my spreadsheet
(This is the webpage version of the excel dividends download:http://ichart.finance.yahoo.com/tabl...=v&ignore=.csv )

Note that the webpage links above use the following conventions a=(startmonth), b=(startday), c=(startyear), d=(endmonth), e=(endday), f=(endyear). For my purposes I believe just using using zeros for a, b, c, d, e, and f is fine as it will just retrieve all of the history for that stock from inception to present (which is perfect for me). Also, the only difference between the Prices URL and the Dividends URL is the "d&ignore" vs. the "v&ignore".

A simple web query won't work because the tables, as displayed on yahoo, span multiple "pages", and also include items that are not included in the files that I download.

Ideally the macro should use the stock I enter in cell C2 of the "Analysis" tab.

I'm sure that the solution is relatively simple to someone with more skill than myself, but I'm completely lost. Can someone please help me out?

Thanks in advance,

Liosis

P.S. I've added a file showing what I am trying to accomplish. The Yellow highlights in the picture relate to step #3 above while the green highlights relate to step #5 above. I'm simply wanting to automate the importing of the data at the above mentioned URLs to the placement in the attached picture.

[Liosis]

Looks like I got an answer to what I was looking for at another site from a very helpful member. Thought I'd share it here as well if anyone is interested...it works great.

Please Login or Register  to view this content.

[arlu1201]

Liosis,

Remember for future reference -

Rule 8 of our Forum rules states - Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[Liosis]

I wasn't aware of that rule, but did update the post for comments/solutions from the other forum. Anyways, here is the link to the crosspost:
http://www.mrexcel.com/forum/excel-q...utomation.html

Thanks,

Liosis