Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
what is the excel formula for market volatility?
anybody knows the formula??
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
what is the excel formula for market volatility?
i am not quite sure what u mean
while there is no this type in the excel help,maybe u mean the statistic formula ,such as Normsdist(),stdev() and so on. BTW which one u choose is relate to your make volatility model. HTH Regards Chelsea "Dyn" ... anybody knows the formula?? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
what is the excel formula for market volatility?
"Dyn" wrote:
anybody knows the formula?? You ask: "What is the excel formula for market volatility?". (In the future, always put your question in the text body, even if it duplicates the subject line.) There is no Excel function for market volatility. In fact, there is no single mathematical formula for market volatility. To answer your question: decide on the mathematical formula that you want to use for market volatility, then write the Excel formula that implements it. Sometimes, market volatility is defined as the standard deviation (of something; TBD depending on context). The Excel function is STDEV(). |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
what is the excel formula for market volatility?
Hi
Not sure what you mean by market volatility. If it is to do with moevments in share prices, I can't help you. If you mean the Price Elasticity of Demand, then there is not an inbuilt formula, but you can use Excel to calculate the value for you. Price Elasticity of Demand = % change in demand / % change in price To calculate the percent change in demand, find the difference between the quantities purchased at each price and divide the difference by the average of the two quantities: Step 1 (Quantity2 - Quantity1) / ((Quantity1 + Quantity2)/2) To calculate the percent change in price, find the difference between the two product prices and divide the difference by the average of the two prices: Step 2 (Price2 - Price1) / ((Price1 + Price2)/2) Dividing the percent change in demand (result of step 1) by the percent change in price (result of step 2) provides the price elasticity of demand for the product. -- Regards Roger Govier "Dyn" wrote in message ... anybody knows the formula?? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
what is the excel formula for market volatility?
If you mean as defined at http://en.wikipedia.org/wiki/Volatility_%28finance%29
you could use the array formula (committed with Ctrl-Shift-Enter) =STDEV(LOG(data))*SQRT(tradingDaysPerYear) to get annualized volatility. Jerry "Dyn" wrote: anybody knows the formula?? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
what is the excel formula for market volatility?
On Sep 13, 10:56 am, Jerry W. Lewis
wrote: If you mean as defined athttp://en.wikipedia.org/wiki/Volatility_%28finance%29 you could use the array formula (committed with Ctrl-Shift-Enter) =STDEV(LOG(data))*SQRT(tradingDaysPerYear) to get annualized volatility. I notice that you (and the Wiki entry) do not convert that back to a std dev of the returns (i.e. exp(stdev(ln(data))...)). Is that conventional? If so, would you also leave the mean in terms of the log return (e.g. average(log(data)))? I also wonder what financial software really wants when it asks us to input, variously, "volatility" or "std dev" along with the "mean". |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
what is the excel formula for market volatility?
I can't speak to financial convention, since I don't work in that area.
However, for most things that you would want to do with a standard deviation, back-transforming the standard deviation itself would not be useful. For instance, if you had to transform your data to achieve approximate normality, you would calculate confidence intervals, etc on the thransformed scale and back-transform those limits rather than the standard deviation itself. Jerry "joeu2004" wrote: On Sep 13, 10:56 am, Jerry W. Lewis wrote: If you mean as defined athttp://en.wikipedia.org/wiki/Volatility_%28finance%29 you could use the array formula (committed with Ctrl-Shift-Enter) =STDEV(LOG(data))*SQRT(tradingDaysPerYear) to get annualized volatility. I notice that you (and the Wiki entry) do not convert that back to a std dev of the returns (i.e. exp(stdev(ln(data))...)). Is that conventional? If so, would you also leave the mean in terms of the log return (e.g. average(log(data)))? I also wonder what financial software really wants when it asks us to input, variously, "volatility" or "std dev" along with the "mean". |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
what is the excel formula for market volatility?
On Sep 13, 12:10 pm, Jerry W. Lewis
wrote: I can't speak to financial convention, since I don't work in that area. Fair enough. I am in a similar situation. However, for most things that you would want to do with a standard deviation, back-transforming the standard deviation itself would not be useful. Oh, I agree; that is, we would have to continually convert back and forth. And I ass-u-me that financial software does the right thing. I was just asking about the convention for the __input__, if there is any. I have used free online software (read: poorly documented) that asks for "average return" and "volatility" or "standard deviation". I would expect the two should be expressed in terms of the same "space", be it the returns or the log returns. And in the early days of my learning in this area (some years ago), google searchs turned up lengthly explanations that converted the average log return to the geometric mean of the return and similarly for the standard deviation of the log returns. (And of course, some software that I have used want the beta for "volatility", which is something else entirely.) If the world were a consistent place, the software would ask for statistics about the "returns" or the "log returns" per se. That would clear things up. But except for some educational web sites, I have never seen the term "log return" used explicity -- for example, never in sales literature that includes performance data. Oh well, I suspect there is no canonical answer to my question. I really just wanted to raise the visibility of the fact that the definition "volalitity" as the sd of the log return, per se, is not necessary so cut-and-dried. At least, that has been my experience. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
what is the excel formula for market volatility?
I think you still have to learn about how to calculate the market volatility. The standard deviation is used to calculate the volatility of the market and the Newton Raphson method will calculate it. There is not any formula in excel to calculate market volatility.
Be precise in your question. http://www.livevol.com/options-excel.html On Tuesday, September 11, 2007 9:06 PM Dy wrote: anybody knows the formula?? On Tuesday, September 11, 2007 10:09 PM chelsea wrote: i am not quite sure what u mean while there is no this type in the excel help,maybe u mean the statistic formula ,such as Normsdist(),stdev() and so on. BTW which one u choose is relate to your make volatility model. HTH Regards Chelsea "Dyn" ... On Wednesday, September 12, 2007 12:06 AM joeu2004hotmailco wrote: "Dyn" wrote: You ask: "What is the excel formula for market volatility?". (In the future, always put your question in the text body, even if it duplicates the subject line.) There is no Excel function for market volatility. In fact, there is no single mathematical formula for market volatility. To answer your question: decide on the mathematical formula that you want to use for market volatility, then write the Excel formula that implements it. Sometimes, market volatility is defined as the standard deviation (of something; TBD depending on context). The Excel function is STDEV(). On Wednesday, September 12, 2007 5:16 AM Roger Govier wrote: Hi Not sure what you mean by market volatility. If it is to do with moevments in share prices, I can't help you. If you mean the Price Elasticity of Demand, then there is not an inbuilt formula, but you can use Excel to calculate the value for you. Price Elasticity of Demand = % change in demand / % change in price To calculate the percent change in demand, find the difference between the quantities purchased at each price and divide the difference by the average of the two quantities: Step 1 (Quantity2 - Quantity1) / ((Quantity1 + Quantity2)/2) To calculate the percent change in price, find the difference between the two product prices and divide the difference by the average of the two prices: Step 2 (Price2 - Price1) / ((Price1 + Price2)/2) Dividing the percent change in demand (result of step 1) by the percent change in price (result of step 2) provides the price elasticity of demand for the product. -- Regards Roger Govier "Dyn" wrote in message ... On Thursday, September 13, 2007 1:56 PM post_a_repl wrote: If you mean as defined at http://en.wikipedia.org/wiki/Volatility_%28finance%29 you could use the array formula (committed with Ctrl-Shift-Enter) =STDEV(LOG(data))*SQRT(tradingDaysPerYear) to get annualized volatility. Jerry "Dyn" wrote: On Thursday, September 13, 2007 2:34 PM joeu2004 wrote: On Sep 13, 10:56 am, Jerry W. Lewis wrote: I notice that you (and the Wiki entry) do not convert that back to a std dev of the returns (i.e. exp(stdev(ln(data))...)). Is that conventional? If so, would you also leave the mean in terms of the log return (e.g. average(log(data)))? I also wonder what financial software really wants when it asks us to input, variously, "volatility" or "std dev" along with the "mean". On Thursday, September 13, 2007 3:10 PM post_a_repl wrote: I can't speak to financial convention, since I don't work in that area. However, for most things that you would want to do with a standard deviation, back-transforming the standard deviation itself would not be useful. For instance, if you had to transform your data to achieve approximate normality, you would calculate confidence intervals, etc on the thransformed scale and back-transform those limits rather than the standard deviation itself. Jerry "joeu2004" wrote: On Thursday, September 13, 2007 3:43 PM joeu2004 wrote: On Sep 13, 12:10 pm, Jerry W. Lewis wrote: Fair enough. I am in a similar situation. Oh, I agree; that is, we would have to continually convert back and forth. And I ass-u-me that financial software does the right thing. I was just asking about the convention for the __input__, if there is any. I have used free online software (read: poorly documented) that asks for "average return" and "volatility" or "standard deviation". I would expect the two should be expressed in terms of the same "space", be it the returns or the log returns. And in the early days of my learning in this area (some years ago), google searchs turned up lengthly explanations that converted the average log return to the geometric mean of the return and similarly for the standard deviation of the log returns. (And of course, some software that I have used want the beta for "volatility", which is something else entirely.) If the world were a consistent place, the software would ask for statistics about the "returns" or the "log returns" per se. That would clear things up. But except for some educational web sites, I have never seen the term "log return" used explicity -- for example, never in sales literature that includes performance data. Oh well, I suspect there is no canonical answer to my question. I really just wanted to raise the visibility of the fact that the definition "volalitity" as the sd of the log return, per se, is not necessary so cut-and-dried. At least, that has been my experience. Submitted via EggHeadCafe SharePoint 2010 Using External List http://www.eggheadcafe.com/tutorials...rnal-list.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to plot volatility in Excel | Charts and Charting in Excel | |||
present market value for homes in excel | Excel Worksheet Functions | |||
volatility calculation | Excel Worksheet Functions | |||
volatility | Excel Discussion (Misc queries) | |||
Excel should have an open/close high/low chart for stock market u. | Charts and Charting in Excel |