![]() The beta of 0.42 is from Yahoo! Finance.) 3. (For example, using CAPM, the estimated required rate of return as 3% + 0.42 ( 10% − 3% ) = 5.94%. Assume a market rate of return of 10%, and for the risk-free rate, use the latest three-month Treasury bill rate. Use the CAPM to find the required rate of return on your stock. For example, the 2013 dividend $2.08 is entered as P V, the 2018 dividend $2.37 as F V, 5 as n and solve for i, the growth rate of 2.65\%). (The growth rate can be figured out using the financial calculator or Excel based on the dividends. Use the historical and forecasted data reported in the source you select along with the Dividend Growth Model, and calculate the maximum (i.e., justified) price you'd be willing to pay for this stock. Pick a company that's been publicly traded for at least 10 years (e.g. W.Cells(i + 2, 5).Select a company from Yahoo! Finance or another online source. W.Cells(i + 2, 4).Value = Values(UBound(Values) - 1) W.Cells(i + 2, 3).Value = Values(UBound(Values) - 2) StartDate = Left(StartDate, Len(StartDate) - 1)ĮndDate = EndDate & W.Range("H" & i).Value & "+"ĮndDate = Left(EndDate, Len(EndDate) - 1)ĭim URL As String: URL = "/table.csv?s" & Symbols & "&f=snl1hg"ĭim Resp As String: Resp = Http.ResponseTextĭim Lines As Variant: Lines = Split(Resp, vbNewLine) StartDate = StartDate & W.Range("H" & i).Value & "+" Symbols = Left(Symbols, Len(Symbols) - 1) ![]() Symbols = Symbols & W.Range("A" & i).Value & "+" 'Cycle through and set up symbol and date variables 'Pulls the stock symbols up until the last row regardless of the amount of entriesĭim Last As Integer: Last = W.Range("A10000").End(xlUp).Row I want to reference a date in column H and pull the data for that day. Below is the code if anyone has any thoughts. I made a few changes to the code but they didn't seem to work and now I'm somewhat lost. So I was able to get the pull to work for current date only. honest!Īpplication.DisplayStatusBar = sbState ' return control to origina state Range(Chr(Asc("B") + 1 + UBound(stockData)) & i).Value = stockTimeĪpplication.StatusBar = "Resetting calculation state."Īpplication.Calculation = appCalcStatus ' restore calculation mode tooĪpplication.StatusBar = False ' this RESTORES default text to the status bar. Range(Chr(Asc("B") + UBound(stockData)) & i).Value = stockDate Range(Chr(Asc("B") + n) & i).Value = stockData ' Now assign values out to cells the current row (B to F, then G) ' Resets status bar text if GetQUoteFromXml was tweaking it StockTime = TimeValue(GetQuoteFromXml(stockXml, "LastTradeTime")) StockDate = CDate(GetQuoteFromXml(stockXml, "LastTradeDate")) StockData(4) = Val(GetQuoteFromXml(stockXml, "Volume")) StockData(3) = Val(GetQuoteFromXml(stockXml, "LastTradePriceOnly")) StockData(2) = Val(GetQuoteFromXml(stockXml, "DaysLow")) StockData(1) = Val(GetQuoteFromXml(stockXml, "DaysHigh")) StockData(0) = Val(GetQuoteFromXml(stockXml, "Open")) ' Could not find it - all 0's and set date to today Set stockXml = GetQuoteXmlFromWeb(ActiveCell.Value) ' For each stock row, get the XML data for the stock and write it to the rowĪpplication.StatusBar = "Get quote for: " & ActiveCell.Value ' Capture the row number, then start the loop ' Activate the sheet and get to the last row ![]() SbState = Application.DisplayStatusBar ' save current stateĪpplication.DisplayStatusBar = True ' take over status barĪpplication.StatusBar = "Preparing quote request."Īpplication.Calculation = xlCalculationManual Sub UpdatePriceData(Optional manageCalcStatus As Boolean = True)ĭim stockData(5) As Double ' Open, High, Low, Volume For some purposes (such as historical volatility calculation) it is more useful. ![]() ' manageCalcStatus = TRUE if we should turn Autocalc off then restore, or FALSE if caller does it Yahoo Finance Website How to Find the Right Symbol on Yahoo Finance. ' for all sequential symbols in A2 and on down. I know this doesn't provide exactly what I want but I was hoping to get it working and then worry about making the adjustments As a side, I found the below code at VBA Macros Provide Yahoo Stock Quote Downloads in Excel 2007 - CodeProject Fair value is the appropriate price for the shares of a company, based on its earnings and growth rate.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |