c# pdf library github : Modify pdf metadata SDK control API wpf web page html sharepoint Wiley%20Advanced%20Modelling%20in%20Finance%20using%20Excel%20and%20VBA1-part580

1
Introduction
We hope that our text, Advanced Modelling in Finance, is conclusive proof that a wide
range ofmodels can now be successfully implemented using spreadsheets. The models
rangeacrossthecompletespectrumoffinanceincludingequities,equityoptionsandbond
options spanning developments fromthe earlyfifties to the latenineties. The models are
implementedinExcelspreadsheets,complementedwithfunctions writtenusingtheVBA
language withinExcel. The resultinguser-defined functions provide aportablelibraryof
programs withmore than sufficient speedandaccuracy.
Advanced Modelling inFinance should be viewed as a complement (or dare we say,
an antidote) to traditional textbooks in the area. It contains relatively few derivations,
allowing us to cover a broader range of models and methods, with particular emphasis
onmore recentadvances.
The major theoretical developments in finance such as portfolio theory in the 1950s,
thecapitalassetpricingmodelinthe1960sandtheBlack–Scholes formulainthe 1970s
broughtwiththemanalyticsolutionsthatarenowstraightforwardtocalculate.Thesubse-
quentdecadeshaveseenagrowingbodyofdevelopmentsinnumericalmethods. Withan
intelligentchoice ofparameters, binomial trees have assumed a centralrolein themore
numerically-intensive calculations now required to value equity and bond options. The
centreofgravityinfinancenowconcernsthesearchformoreefficientwaysofperforming
such calculationsratherthan the theories from yesteryear.
The breadth of the coverage across finance and the sophistication needed for some
ofthemore advanced models are testamentto the abilityofExcel, the built-in functions
containedinExcelandtherealprogrammingenvironmentthatVBAprovides.Thisallows
us to highlight the commonality of assumptions (lognormality), mathematical problems
(expectation) and numerical methods (binomial trees) throughout finance as a whole.
Withoutexception, we have tried to ensure a consistent and simple notation throughout
thebooktoreinforce this commonality andtoimprove clarityofexposition.
Our objective in writing a book that covers the broad range of subjects in finance
has proved to be both a challenge and an opportunity. The opportunity has provided
us with the chance to overview finance as a whole and, in so doing, to make impor-
tant connections and bring out commonalities in asset price assumptions, mathemat-
ical problems, numerical methods and Excel solutions. In the following sections we
summarise a few of these unifying insights that apply to equities, options and bonds
withregardto finance, mathematicaltopics, numericalmethods and Excelfeatures. This
is followed by a more detailed summary of the main topics covered in each chapter of
thebook.
1.1 FINANCE INSIGHTS
The genesis of modern finance as a subject separate from economics started with
Markowitz’s development of portfolio theory in 1952. Markowitz used utility theory to
modelthepreferencesofindividualinvestors and to developa mean–variance approach
Modify pdf metadata - add, remove, update PDF metadata in C#.net, ASP.NET, MVC, Ajax, WinForms, WPF
Allow C# Developers to Read, Add, Edit, Update and Delete PDF Metadata
modify pdf metadata; pdf xmp metadata
Modify pdf metadata - VB.NET PDF metadata library: add, remove, update PDF metadata in vb.net, ASP.NET, MVC, Ajax, WinForms, WPF
Enable VB.NET Users to Read, Write, Edit, Delete and Update PDF Document Metadata
add metadata to pdf; remove metadata from pdf acrobat
2
AdvancedModellingin Finance
to examining the trade-off between return (as measured by an asset’s mean return) and
risk(measuredbyanasset’svarianceofreturn).Thissubsequentlyledtothedevelopment
bySharpe,LintnerandTreynorofthecapitalassetpricingmodel(CAPM),anequilibrium
modeldescribingexpectedreturns on equities. The CAPM introduced beta as ameasure
ofdiversifiablerisk, arguingthatthecreationofportfoliosservedtominimisethespecific
risk elementoftotalrisk (variance).
Thenextgreattheoreticaldevelopmentwastheequityoptionpricingformula ofBlack
andScholes,whichrestedontheabilitytocreatea(riskless)hedgeportfolio.Contempora-
neously, Merton extendedthe Black–Scholes formula to allow forcontinuous dividends
and thus also options on commodities and currencies. The derivation of the original
formularequiredthesolvingofthediffusion(orheat)equationfamiliarfromphysics,but
was subsequently encompassed by the broader risk-neutralapproach to the valuationof
derivatives.
1.2 ASSET PRICE ASSUMPTIONS
Although portfolio theorywas derivedthrough individualpreferences, itcould alsohave
been obtained by making assumptions about the distribution of asset price returns. The
standardassumptionisthatequityreturnsfollowalognormaldistribution–equivalentlywe
cansay thatequity log returns follow anormal distribution. More recently, practitioners
have examined the effect of departures fromstrict normality (as measured by skewness
and kurtosis) andhave also proposeddifferent distributions (forexample, the reciprocal
gammadistribution).
Althoughbonds have characteristicsthat are differentfromequities, the starting point
forbondoption valuationis the shortinterestrate. This is frequentlyassumed tofollow
the lognormalornormaldistribution. Theresultisthatfamiliarresultsgroundedinthese
probabilitydistributions canbeappliedthroughoutfinance.
1.3 MATHEMATICAL AND STATISTICAL PROBLEMS
Within the equities part, the mathematical problems concern optimisation. The optimi-
sation can also include additional constraints, exemplified by Sharpe’s development of
returns-basedstyleanalysis.Betaisestimatedastheslopecoefficientinalinearregression.
Optionsarevaluedintherisk-neutralframeworkasstatisticalexpectations.Thenormal
distribution of log equity prices can be approximated by an equivalent discrete bino-
mial distribution. This binomial distribution provides the framework for calculating the
expectedoption value.
1.4 NUMERICAL METHODS
Inthecontextofportfoliooptimisation, theoptimisationinvolvesportfoliovariance,and
the numerical method needed foroptimisation is quadratic programming. Style analysis
alsouses quadraticprogramming, the quantityto be minimisedbeingtheerrorvariance.
Although not usually thought of as optimisation, linear regression chooses slope coef-
ficients to minimise residual error. Here optimisation is of a different kind, regression
analysis, whichprovides analyticalformulas tocalculatethebetacoefficients.
Turning to option valuation, the binomial tree provides the structure within which
the risk-neutralexpectation canbe calculated. We highlighttheimportanceofparameter
How to C#: Modify Image Metadata (tag)
VB.NET How-to, VB.NET PDF, VB.NET Word, VB.NET Excel, VB.NET PowerPoint, VB.NET Tiff, VB.NET Imaging, VB.NET OCR, VB How to C#: Modify Image Metadata (tag).
search pdf metadata; analyze pdf metadata
VB.NET PDF File & Page Process Library SDK for vb.net, ASP.NET
VB.NET PDF - How to Modify PDF Document Page in VB.NET. VB.NET Guide for Processing PDF Document Page and Sorting PDF Pages Order.
view pdf metadata in explorer; batch pdf metadata editor
Introduction
3
choice by examining the convergence properties of three different binomial trees. Such
treesalso allow the valuationofAmericanoptions, where the optioncanbe exercised at
anydate priorto maturity.
With European options, techniques such as Monte Carlo simulation and numerical
integration arealso used. Numericalsearch methods, inparticulartheNewton–Raphson
approach, ensurethatvolatilitiesimpliedbyoptionpricesinthemarketcanbeestimated.
1.5 EXCEL SOLUTIONS
The spreadsheetsdemonstratehowExcelcanbeusedas aprototypeforbuildingmodels.
Within the individual spreadsheets, all the formulas in the cells can easily be examined
and we have endeavoured to incorporate all intermediate calculations in cells of their
own.Thespreadsheetsalsoallowthehallmarkabilityto‘what-if’bychangingparameter
values in cells.
The implementation of all the models andmethods occurs twice: once in the spread-
sheets andonce intheVBA functions. This dual approachserves as animportant check
onthe accuracyofthenumerical calculations.
SomeoftheVBAproceduresaremacros,normallyseenbyothersasthemainpurpose
ofVBAinExcel.However,themajorityoftheproceduresweimplementareuser-defined
functions. We demonstrate how easily these functions can be written in VBA and how
theycan incorporateExcel functions, includingthepowerfulmatrixfunctions.
The Goal Seekand Solvercommands within Excelareused in the optimisation tasks.
Weshow how these commandscan be automatedusingVBAuser-definedfunctions and
macros. Another under-usedaspect of Excel involves the application of array functions
(invoked by the CtrlCShiftCEnter keystroke combination) and we implement these in
user-definedfunctions.Toimproveefficiency,ourbinomialtreesinuser-definedfunctions
use one-dimensionalarrays (vectors)ratherthantwo-dimensionalarrays (matrices).
1.6 TOPICS COVERED
Therearefourpartsinthebook,thefirstpartillustratingtheadvancedmodellingfeatures
inExcel followedbythree parts withapplications in finance.Thethree partsonapplica-
tions coverequities, options onequities andoptions onbonds.
Chapter2 emphasisesthe advanced Excelfunctionsand techniquesthatwe use in the
remainder of the book. We pay particular attention to the array functions within Excel
andprovide ashortsectiondetailingthemathematics underlyingmatrixmanipulation.
Chapter3introducestheVBAprogrammingenvironmentandillustratesastep-by-step
approachtothewritingofVBAsubroutines (macros). Theexamples chosendemonstrate
howmacros canbe usedtoautomate andrepeattasks in Excel.
Chapter4 moves on to VBA user-defined functions, which have a crucial role
throughout the applications in finance. We emphasise how to deal with both scalar
and array variables–as input variables to VBA functions, their use in calculations and
finally as output variables. Again, we use a step-by-step approach for a number of
examples. Inparticular, wewrite user-defined functions to value bothEuropeanoptions
(the Black–Scholes formula)andAmericanoptions (binomial trees).
Chapter5 introduces thefirstapplicationpart, thatdealing with equities.
Chapter6 covers portfolio optimisation, using both Solverand analytic solutions. As
willbecome the norm in the remainingchapters, Solver is used both inthe spreadsheet
C# PDF File & Page Process Library SDK for C#.net, ASP.NET, MVC
Image: Copy, Paste, Cut Image in Page. Link: Edit URL. Bookmark: Edit Bookmark. Metadata: Edit, Delete Metadata. C# PDF - Modify PDF File and Page Using C#.
c# read pdf metadata; embed metadata in pdf
C# Raster - Modify Image Palette in C#.NET
in PDF, C#.NET edit PDF bookmark, C#.NET edit PDF metadata, C#.NET VB.NET How-to, VB.NET PDF, VB.NET Word, VB.NET C# Raster - Modify Image Palette in C#.NET.
preview edit pdf metadata; delete metadata from pdf
4
AdvancedModellingin Finance
andautomatedinaVBAmacro.ByusingthearrayfunctionsinExcelandVBA,wedetail
how the points on the efficientfrontiercan be generated. The developmentof portfolio
theoryis dividedinto threegeneric problems, whichrecurinsubsequentchapters.
Chapter7 looks at (equity) asset pricing, starting with the single-index model and
the capital asset pricing model (CAPM)and concluding with Value-at-Risk (VaR). This
introduces the assumption that asset log returns follow a normal distribution, another
recurrenttheme.
Chapter8 covers performance measurement, again ranging from single-parameter
measuresusedintheveryearliestdaystomulti-indexmodels(suchasstyleanalysis)that
representcurrentbestpractice.Weshow, forthe firsttime ina textbook, howconfidence
intervals canbe determined fortheassetweights fromstyleanalysis.
Chapter9introduces thesecondapplicationpart, thatdealingwithoptionsonequities.
Buildingonthenormaldistributionassumedforequitylogreturns,wedetailthecreation
of the hedge portfoliothatis thekey insight behindthe Black–Scholes option valuation
formula. The subsequent interpretation of the option value as the discounted expected
value ofthe optionpayoffina risk-neutralworldis alsointroduced.
Chapter10 looks at binomial trees, which can be viewed as a discrete approxima-
tion to the continuous normal distribution assumed for log equity prices. In practice,
binomial trees formthe backbone ofnumerical methods for option valuation since they
cancopewithearlyexerciseand hence thevaluation ofAmericanoptions. We illustrate
threedifferentparameterchoicesforbinomialtrees,includingthelittle-knownLeisenand
Reimertreethathasvastlysuperiorconvergenceandaccuracypropertiescomparedtothe
standardparameterchoices. Weuse anine-steptreeinourspreadsheetexamples,butthe
user-definedfunctionscancopewith anynumberofsteps.
Chapter11 returns to the Black–Scholes formula and shows both its adaptability
(allowing options on assets such as currencies and commodities to be valued) and its
dependenceonthe asset priceassumptions.
Chapter12 covers two alternative ways of calculating the statistical expectation that
lies behind the Black–Scholes formula for European options. These are Monte Carlo
simulation and numerical integration. Although these perform less well for the simple
options we consider, eachofthese methods has avaluable role inthevaluation of more
complicated options.
Chapter13movesawayfromtheassumptionofstrictnormalityofassetlogreturnsand
shows howsuchdeviation(typicallythroughdifferingskewness andkurtosisparameters)
leads to the so-called volatility smile seen in the market prices of options. Efficient
methodsforfindingtheimpliedvolatilityinherentinEuropeanoptionpricesaredescribed.
Chapter14 introduces the third application part, that dealing with options on bonds.
While bond prices have characteristics that are different from equity prices, there is a
lot ofcommonality in the mathematical problems and numerical methods used to value
options. Wedefinethe term structure based ona series of zero-couponbondprices, and
show howthe short-terminterestratecan be modelledin abinomial tree as a means of
valuingzero-couponbondcashflows.
Chapter15coverstwomodelsforinterestrates,thoseofVasicekandCox,andIngersoll
and Ross. We detailanalytic solutions forzero-couponbond prices and options on zero-
couponbonds togetherwithan iterative approach tothe valuationofoptions on coupon
bonds.
Chapter16 shows how the short rate can be modelled in a binomial tree in order
to match a given term structure of zero-coupon bond prices. We build the popular
How to C#: Modify Image Bit Depth
text in PDF, C#.NET edit PDF bookmark, C#.NET edit PDF metadata, C#.NET VB.NET How-to, VB.NET PDF, VB.NET Word, VB.NET Excel How to C#: Modify Image Bit Depth.
remove metadata from pdf; pdf xmp metadata viewer
How to C#: Overview of Using XImage.Raster
See this C# guide to learn how to use RasterEdge XImage SDK for .NET to edit the image file Metadata, Color Palette and modify the compression method.
change pdf metadata creation date; pdf metadata
Introduction
5
Black–Derman–Toy interest rate tree(both inthe spreadsheet andin user-defined func-
tions) and show how it can be used to value both European and American options on
zero-couponbonds.
ThefinalAppendixisaPandora’sboxofotheruser-definedfunctions,thatarelessrele-
vantto the chosenapplications in finance. Nevertheless theyconstitute a usefultoolbox,
including as they do functions for ARIMA modelling, splines, eigenvalues and other
calculation procedures.
1.7 RELATED EXCEL WORKBOOKS
Part IwhichconcentratesonExcelfunctions andproceduresandunderstandingVBAhas
three related workbooks, AMFEXCEL, VBSUB and VBFNS which accompany Chap-
ters2, 3 and 4respectively.
PartIIonequities has threerelatedworkbooks, EQUITY1, EQUITY2 andEQUITY3
whichaccompany Chapters6, 7 and8respectively.
PartIII on options on equities has four files, OPTION1, OPTION2, OPTION3 and
OPTION4whichaccompanyChapters 10, 11, 12and13respectively.
PartIVonbonds has tworelatedworkbooks, BOND1andBOND2whichaccompany
Chapters 14, 15 and16as indicatedin the text.
The Appendixhas one workbook, OTHERFNS.
1.8 COMMENTS AND SUGGESTIONS
Havingspentsomuchtimedevelopingthematerialandwritingthisbook,wewouldvery
much appreciate any comments, suggestions and, dare we say, possible corrections and
improvements.Pleaseemailmstaunton@london.eduorfindyourwaytowww.london.edu/
ifa/services/services.html or www.business.city.ac.uk/irmi/mstaunton.html.
How to C#: Modify Color and Contrast
How to C#: Modify Color and Contrast. Overview for How to Modify Color and Contrast. Overview. By two. Steps to Modify Color and Contrast.
remove metadata from pdf file; pdf metadata viewer
VB.NET PDF Library SDK to view, edit, convert, process PDF file
NET developers can redact, delete, view and save PDF metadata. added to a specific location on PDF file page addition, you can easily create, modify, and delete
view pdf metadata; pdf metadata editor online
Part One
Advanced Modelling in Excel
C# PDF Library SDK to view, edit, convert, process PDF file for C#
NET allows you to read, add, edit, update, and delete PDF file metadata, like Title In addition, you can easily create, modify, and delete PDF annotations.
change pdf metadata; remove pdf metadata
How to C#: Modify Alpha Channel
Image Access and Modify. Image Information. Metadata(tag) Edit. |. Home ›› XImage.Raster ›› C# Raster: Modify Alpha Channel. PDF in C#, C# convert PDF to HTML
google search pdf metadata; pdf remove metadata
2
Advanced Excel Functions and Procedures
The purpose of this chapter is to review certain Excel functions and procedures used
in the text. These include mathematical, statistical and lookup functions from Excel’s
extensive range of functions, as well as much-used procedures such as setting up Data
Tables and displaying results in XY charts. Also included are methods of summarising
data sets, conducting regression analyses, and accessing Excel’s Goal Seek and Solver.
The objective is to clarify and ensure that this material causes the reader no difficulty.
The advanced Excel user may wish to skim the content or use the chapter for further
referenceas and when required. Tomakethe various topics moreentertainingandmore
interactive,a workbookAMFEXCEL.xls includestheexamples discussedinthetextand
allowsthe readertocheckhis orherproficiency.
2.1 ACCESSING FUNCTIONS IN EXCEL
Excel provides many worksheetfunctions, whichareessentiallycalculation routines that
havebeencodedup.Theyareusefulforsimplifyingcalculationsperformedinthespread-
sheet,andalsoforcombiningintoVBAmacrosanduser-definedfunctions(topicscovered
inChapters3 and4).
ThePasteFunctionbutton(labelledfx)onthestandardtoolbargivesaccesstothem.(It
waspreviouslyknownasthefunctionwizard.)AsFigure2.1shows,functionsaregrouped
intodifferentcategories: mathematical, statistical, logical, lookup andreference,etc.
Figure 2.1 Paste FunctiondialogboxshowingtheCOMBINfunctionin the Math category
10
AdvancedModellinginFinance
Here the Math & Trig function COMBIN has been selected, which produces a brief
description ofthe function’s inputs and outputs. Fora fuller description, press the Help
button(labelled?).
OnclickingOK,theFormulapaletteappearsprovidingslotsforenteringtheappropriate
inputs, as in Figure 2.2. The required inputs can be keyed into the slots (as here) or
‘selected’byreferencing cells inthespreadsheet(byclickingthe buttons to collapse the
Formula palette). Note that the palette can be dragged awayfrom its standard position.
Clickingthe OK button on the palette orthe tick on the Editline enters the formula in
the spreadsheet.
Figure2.2 BuildingtheCOMBINfunctionintheFormula palette
As wellas the Formula palette withinputs forfunctionCOMBIN, Figure 2.2shows the
constructionofthecellformulaontheEditline,withthePasteFunctionbuttondepressed
(inaction).Notice alsothe PasteNamebutton(labelledDab)whichfacilitates pastingof
namedcells into the formula. (Attaching names toranges andreferencingcellranges by
names is reviewedinsection2.10.)
As well as all Excelfunctions, the Paste Function button also provides access to the
user-definedcategory offunctions which are described in Chapter4.
Having discussed how to access the functions, in the following sections we describe
some specific mathematicalandstatisticalfunctions.
2.2 MATHEMATICAL FUNCTIONS
WithintheMath&Trigcategory,wemakeuseoftheEXP(x),LN(x),SQRT(x),RAND(),
FACT(x)and COMBIN(number, number
chosen)functions.
EXP(x)returnsvalues of the exponential function, exp(x)ore
x
.Forexample:
ž EXP(1)returns value of e(2.7183 when formattedtofourdecimalplaces)
ž EXP(2)returns value of e
2
(7.3891tofourdecimalplaces)
ž EXP(1)returns valueof1/e ore
1
(0.36788 tofive decimal places)
AdvancedExcel FunctionsandProcedures
11
Infinancecalculations, cash flows occurring atdifferenttime periods are convertedinto
future(orpresent)valuesbyapplyingcompounding(ordiscounting)factors.Withcontinu-
ous compounding at rate r, the compounding factor for one year is exp(r), and the
equivalentannualinterestrater
a
,ifcompoundingweredoneonanannualbasis, isgiven
bythe expression:
r
a
Dexpr1
Continuous compounding and the use of the EXP function is illustrated further in
section2.7.1onDataTables.
LN(x)returns the naturallogarithmofvaluex.Note thatx mustbepositive, otherwise
thefunctionreturns #NUM!fornumeric overflow. Forexample:
ž LN(0.36788)returns value1
ž LN(2.7183)returnsvalue1
ž LN(7.3891)returnsvalue2
ž LN(4)returnsvalue#NUM!
In finance, we frequently work with (natural) log returns, applying the LN function to
transformthe returns dataintologreturns.
SQRT(x)returns thesquareroot ofvalue x. Clearly, x mustbepositive, otherwise the
functionreturns#NUM!fornumeric overflow.
RAND()generatesauniformlydistributedrandomnumbergreaterthanorequaltozero
andlessthanone. Itchangeseachtimethespreadsheetrecalculates.WecanuseRAND()
tointroduceprobabilistic variabilityintoMonteCarlosimulationofoptionvalues.
FACT(number)returns the factorial ofthe number, which equals 1
Ł
2
Ł
3
Ł
...
Ł
number.
Forexample:
ž FACT(6)returnsthe value 720
COMBIN(number, number
chosen) returns the number of combinations (subsets of
size‘number
chosen’)thatcanbemadeupfroma‘number’ofitems.Thesubsetscanbe
inanyinternalorder.Forexample, ifasharemoveseither‘up’or‘down’atfourdiscrete
times, the numberofsequences withthreeups (andone down)is:
COMBIN4,1D4orequallyCOMBIN4,3D4
that is the four sequences ‘up-up-up-down’, ‘up-up-down-up’, ‘up-down-up-up’ and
‘down-up-up-up’. In statistical parlance, COMBIN4,3 is the numberof combinations
ofthree items selectedfromfourand is usuallydenotedas
4
C
3
(oringeneral,
n
C
r
).
Excel has functions to transpose matrices, to multiply matrices and to invert square
matrices. The relevantfunctions are:
ž TRANSPOSE(array)whichreturnsthe transpose ofanarray
ž MMULT(array1, array2)whichreturnsthe matrix productoftwo arrays
ž MINVERSE(array)which returns thematrix inverseofan array
These fall in the same Math category. Since some readers may need an introduction to
matrices before examining thefunctions, this materialhas been placed at the end of the
chapter(seesection2.13).
12
AdvancedModellinginFinance
2.3 STATISTICAL FUNCTIONS
Excel has several individual functions for quickly summarising the features of a data
set(an‘array’inExcelterminology). Theseinclude AVERAGE(array)whichreturns the
mean, STDEV(array)forthestandarddeviation, MAX(array) and MIN(array)which we
assumearefamiliartothereader.
Toobtainthe distributionofa moderate sizeddata set,thereare some usefulfunctions
thatdeservetobebetterknown. Forexample,theQUARTILEfunctionproducestheindi-
vidualquartilevaluesonthebasisofthepercentilesofthedatasetandtheFREQUENCY
functionreturns the whole frequency distributionofthedatasetaftergrouping.
Excel also provides functions for a range of different theoretical probability distri-
butions, in particularthose for the normal distribution: NORMSDIST and NORMSINV
for the standard normal with zero mean and standard deviation one; NORMDIST and
NORMINVforany normal distribution.
Other useful functions in the statistical category are those for two variables, which
provide many individual quantities used in regression and correlation analysis. For
example:
ž INTERCEPT(known
y’s,known
x’s)
ž SLOPE(known
y’s, known
x’s)
ž RSQ(known
y’s, known
x’s)
ž STEYX(known
y’s, known
x’s)
ž CORREL(array1, array2)
ž COVAR(array1,array2)
There is also a little known array function, LINEST(known
y’s, known
x’s), which
returnstheessentialregressionstatisticsinarrayform. Mostofthesefunctionsareexam-
ined in more detail in section2.11 on regression. Their performance is compared and
contrastedwith the regression outputfromthe DataAnalysis Regression procedure.
Inthenextsection,weexplainhowtousetheFREQUENCY,QUARTILEandvarious
normal functions via examples in theFrequency andSNormsheets of the AMFEXCEL
workbook.
2.3.1 Usingthe FrequencyFunction
FREQUENCY(data
array,bins
array)countshowoftenvaluesinadatasetoccurwithin
specified intervals (or‘bins’), and then returns thesefrequencies ina verticalarray. The
bins
array is the set of intervals into which the values are grouped. Since the function
returns outputin the formofanarray, itis necessarytomarkouta rangeofcells inthe
spreadsheet to receivethe outputbefore entering the function.
Weexplainhow touseFREQUENCYwithanexamplesetoutintheFrequencysheet
ofthe AMFEXCELworkbook. As shownin Figure 2.3, monthly returns andlogreturns
(using the LN function) in columns D10:D71 and E10:E70 have been summarised in
rows 4 to 7. Suppose the aim is to get the frequency distribution of the log returns
(E10:E71), i.e. the so-called ‘data
array’. The objective might be to check that these
returns are approximately normally distributed. First, wehave todecide on intervals (or
bins)forgroupingthedata.Inspectionofthemaximumandminimumlogreturnssuggests
about10 to12intervals intherange0.16to C0.20. The ‘interval’values, which have
Documents you may be interested
Documents you may be interested