Hello All,
First and foremost, I have very little VBA Macro experience and have a couple items I've been trying to accomplish 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
First and foremost, I have very little VBA Macro experience and have a couple items I've been trying to accomplish 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