My question is regarding how I can computer on Excel the last 10 days % chg in a stock. So simply find the percentage difference from the closing price on stock 10 days ago vs. the last traded price. I know I can grab this data from finance.yahoo.com under historical prices, however I have no idea how to grab this data. Also how I would enter this coed in on an excel spreadsheet. Any and all help would be greatly appreciated. I feel like a deer in headlight with excel and am determined to learn more. j
Not 100% sure of the question...but basically... Enter the closing price from 10 days ago in, say, cell A1, then enter the last traded price in another , say, A2 (you can set up a web query to automatically do this, but that's in the advanced class) Then enter the following formula in cell A3 =(A2-A1)/A1 Change the cell formatting to %... I think that ought to do it, but I haven't had my second cup of coffee yet...
Use the OFFSET function If prices are ordered "newest first" (like yahoo finance) then use a positive offset value. Opposite if oldest prices are on top. =L13/OFFSET(L13,10,0)-1 Where L13 contains the most recent price, and your reference price is 10 cells below.
Great, just what I was looking for... Not how do I get excel to grab this 10th day from the last from yahoo finance? Is there a formula, function for that? Im a total newbie, please be patient with me john
I'm sure that there are Visual Basic or .net scripts out there to automate this, but I only know how to do it manually - go to the stock you want at finance.yahoo.com, then click on "historical data" on the left. Once you have the date range you want, there is a small link to "download to spreadsheet." When you click that, it will create a .CSV file which you can directly access, then save as an .xls file. (Note - I am referring to an older version of excel.) Google "yahoo finance price download excel" and you may find a program that automates this process.
Thanks for all your help... As far as entering the closing price from 10 days ago.... is there anyway to do that automatically? Or is there anyway to have excel grab the price 10 days ago for me? i cant imagine inputting the price 10 days ago for 100's of stocks every morning. Thank you all for your help
Hi, if i understand you correct you want excel to automate by a macro to automatically put the close of 10 days ago of x number of stocks in a row/colum or cel. if so you should know a just a little about vba(very easy with the help of google). in vba you can record your actions. When this is on you can load data from a website(probably in the data menu, i have the dutch one so don't know for sure what is called) this creates a querry in lets say cell a1. the url it records is in the vba editor. this one says time and date and the symbol. you should make variables so you can quickly put in x number of stocks and different dates according to a cell in your sheet. No you can simply copy the 10th row(10th close) to a cell and run the process again. Hope this helps if not i could provide you a code......but better to try it first your self because if it fails you have no idea where to look for. gr sintra