Questions tagged [excel]
The excel tag has no usage guidance.
104 questions
10
votes
1
answer
18k
views
How to simulate correlated Geometric brownian motion for n assets?
So I'm trying to simulate currency movements for several currencies with a given correlation matrix. I have the initial price, drift and volatility for each of the separate currencies, and I want to ...
- 103
7
votes
1
answer
4k
views
What is the best solution to use QuantLib within Excel?
Excel is likely the most widespread instrument across all not-only-quants desks; in addition, we have to keep in mind that Bloomberg and Reuters allow to easily import real time data in Excel, and ...
- 2,153
6
votes
2
answers
13k
views
Could someone teach me how to construct the portfolios by compute (like using R, Excel or Eviews)
Recently, I am doing my dissertation that covers asset pricing theory. The empirical test of Fama 3 factors model is an important part of this dissertation. Please let me review the fama model.
Fama ...
5
votes
4
answers
1k
views
Loading HF stock data into excel
Are there any free, open source VBA addins or R packages that can be linked using the yahoo finance/Google finance/other data sources api to continuously download intraday data into excel or R?
https:...
- 2,138
5
votes
1
answer
1k
views
FX Option Price Quotation
I'm trying to replicate the following FX vanilla option pricing exercise (and the conversion between the quote types), taken from Wystup (2006).
A call's value today is well-known given by BS / ...
- 665
5
votes
3
answers
1k
views
Resources for finding quantitative finance examples using excel, VBA and access
I am seeking to increase my knowledge in the quantitative finance field. I would be grateful if someone could point me to useful resource online, where I can find working examples of they types of ...
- 1,398
4
votes
1
answer
892
views
Complex numbers in VBA
Hey I try to price options in VBA. To do this I need to define characteristic function and do some operations on complex numbers. For example I have this code:
...
- 433
4
votes
2
answers
601
views
Finding Lead-Lag Relationship [duplicate]
Given two series of data, volatility and returns, is there a way (Excel) of finding which one is the leading factor and lagging factor?
Thank you for your help.
- 41
4
votes
1
answer
470
views
Holdings based style analysis
This question is not very technical.
I have a file with holdings (both for the fund and the benchmark) of a number of securities and need to do a style allocation analysis.
For these securities, I ...
- 41
4
votes
1
answer
3k
views
Portfolio Optimization with Monte Carlo Simulation - How to do it with Excel?
If I have three asset classes and their historical weekly returns for five years, how can I construct a minimum variance portfolio and an efficient frontier plot with Excel? To do that do I have to ...
- 63
4
votes
1
answer
1k
views
Obtaining logical lists of Bloomberg security codes in Excel
I am using Bloomberg's BDP and BDH functions in excel to retrieve data for a set of options.
The problem is that (as underlying prices move and expiration dates come and go) option strikes are ...
- 151
3
votes
2
answers
5k
views
Excel YIELD function equivalent in python Quantlib
I am struggling to get an equivalent of Excel's YIELD function using Quantlib in python. As you can see from the Excel documentation on YIELD here, only a few parameters are needed compared to this ...
- 139
3
votes
2
answers
577
views
Why does Excel's XIRR() yield a different result than the FFIEC's Reg. Z APR calculator?
I'm hoping someone can help me understand the difference in how Excel calculates the XIRR and how the APR is calculated (according to App. J, Reg. Z in the United States). I'm trying to calculate the ...
- 131
3
votes
2
answers
45k
views
Price at Specific Time from Bloomberg
I have a file where I easily export real-time prices to excel using the Bloomberg Add-In, using the formula BDP. Is there a way to get these prices of a specific time?
For example, =BDP("EURGBP ...
- 31
3
votes
1
answer
2k
views
Export security description data from bloomberg into excel
I have the cusip of about 300 bonds. I want to retrieve the security description from bloomberg and then export it into excel. Does anyone have any tips as to how to accomplish this?
- 31
3
votes
1
answer
3k
views
Which method is implemented by Excel's YEARFRAC for ACT/ACT?
I know the algorithm used by Excel to calculate the YEARFRAC(startDate, endDate, basis) for basis=1. Excel calls the method "act/act". A Java re-implentation of Excel's algorithm can be found at ...
- 1,826
3
votes
3
answers
4k
views
Step By Step method to calculating VaR using MonteCarlo Simulations
In trying to find VaR for 5 financial assets with prices over a long period of time(2000 days worth of data) how would I do the following:
Carry out monte-carlo simulation in order to find a VaR ...
- 31
3
votes
0
answers
1k
views
How to correctly construct a value- and equally weighted portfolio consisting of property-types?
A problem of which I couldn’t find the answer on the forum is about the construction of equally-weighted and value-weighted portfolio.
I want to compute the equally-weighted property-type portfolio ...
- 31
2
votes
1
answer
205
views
Excel PasteSpecial Values shortcut button causing QuantLib to crash
I am experiencing a strange issue with QuantLibXL when I use the quick-access PasteOptions/Values button on the Excel right-click toolbar (apologies for the picture: it is surprisingly hard to capture ...
- 223
2
votes
1
answer
1k
views
Calculating fund alpha using Fama-French 3 factor model?
My dissertation requires me to evaluate fund performance, and for that I need to find the alpha for each fund. I have 173 funds total. I have all the inputs for the 3-factor model, and I realise ...
- 21
2
votes
1
answer
2k
views
Bloomberg API / Excel Add In - Delisted Stocks
So I am using the Bloomberg Excel add in but can also use the API. My dilemma is I have a list of tickers containing SP500 historical constituents and I have retrieved 600 of them from an alternative ...
- 23
2
votes
2
answers
245
views
Single-step Monte Carlo in Excel
How do you simulate correctly using raw prices not returns?
I have corresponding periods of earnings to Futures but the Excel call function =NORMINV(RAND(),mean,stdev) generates negative Futures ...
2
votes
1
answer
2k
views
Implementation of the Chow test in Excel/R
Has anyone used Excel or R Studio to do a simple Chow Test (compare a set of data before and after one particular year)? I have tried to find info but there is not much, especially regarding the use ...
- 23
2
votes
2
answers
13k
views
Bloomberg Libor Rates
I load swap rates data from Bloomberg in excel using the command
=BDH(Ticker,"Px Last",Start_Date,End_Date,"Dts",FALSE)
The tickers I use are "USSW1 Curncy", "...
- 181
2
votes
1
answer
195
views
Bond price using simple interest
I'm trying to model the price of a bond and having some trouble understanding how the "simple" convention works. The bond in question is IL0011948028. I'm able to replicate BBG's price using ...
- 21
2
votes
1
answer
569
views
Building a fundamental equity scoring model based on data from Bloomberg
I have identified around 20 interesting statistics for a universe of stocks, regarding metrics of size, growth, valuation, quality, risk. Think market cap, free float, average daily volume. The ...
- 209
2
votes
1
answer
428
views
excel yearfrac 30u/360 across feb month end
Why are both of these formulas returning 1? One of them should be 3.
=YEARFRAC(DATE(2015,2,27),DATE(2015,2,28),0)*360
=YEARFRAC(DATE(2015,2,28),DATE(2015,3,1),0)*360
Based on every description of ...
2
votes
1
answer
2k
views
How to fully replicate ADX + DI Indicators in Excel? [closed]
For black box testing, I was hoping that I could replicate the ADX + DI+ and DI- indicators that are provided in trading platforms such as ThinkOrSwim, ScottradeElite etc.
However, I noticed that ...
- 121
2
votes
1
answer
2k
views
Data feed for 10 year government bond yields [duplicate]
I am trying to access a data feed for 10 year sovereign bond yields for countries, say the G20. I have tried world bank and IMF data api sources but to no avail.
The data feed is used to update an ...
- 131
2
votes
0
answers
979
views
Extract historic share prices for certain securities at certain points in time with Bloomberg Excel Add-in? [closed]
In order to create some sort of basic framework for everything we are planning on doing in a research project, we would need to get historic intraday data about share prices at certain points in time. ...
- 21
2
votes
0
answers
97
views
Markowitz models with uncertain returns
I am analyzing the Markowitz models with uncertain returns as follows: after calculating the expected returns and the covariances of 30 monthly historical series of 30 stocks, I resolve the Markowitz ...
- 21
2
votes
0
answers
780
views
Backtesting Long/Short Market Neutral Z-Score Strategy with Custom Factors and Custom Stock Universe
So I've managed to backtest simple strategies, like MA, RSI and some fundamental ones (P/E ratios etc) but Im stuck at my last strategy. Here is some information:
Tools: Excel and Python (also a ...
- 21
2
votes
0
answers
410
views
Optimize a trading strategy created in excel with R
I have created quite a complex back test in excel spanning 15 years with 17 parameters. I would like to optimize the parameters which would give me maximum return given a maximum draw-down percentage. ...
2
votes
0
answers
2k
views
How to backtest Value at Risk Models using Conditional and Unconditional tests?
I am trying to carry out backtesting on a number of Value at Risk figures i obtained using var/covar, historical, and monte carlo simulation. The two methods im using are the Kupiec test (...
- 21
2
votes
0
answers
123
views
Review of Excel Stock Simulator
I am currently a senior in high school and I built a stock simulator using knowledge gained from a semester of AP Statistics. I was wondering if someone could tell me if my simulation is legit/...
- 21
1
vote
3
answers
751
views
Future Value: the functions in Excel, Matlab and Numpy Financial don't work when the number of periods is large
The Future Value function and its expected behaviour
Excel's function FV(rate, nper, pmt, pv) calculates the future value of an investment based on periodic, ...
1
vote
2
answers
5k
views
How do you extract only prices for an index from Bloomberg?
I’m trying to get data from an index on Bloomberg terminal into excel with BDH command. Specifically I am trying to get all of the closing prices for each company in the Russell 2000 index for every ...
1
vote
2
answers
1k
views
Automatically Import Stock Data - Yahoo Finance & Microsoft Excel
How to import Growth Estimates: Next 5 years(per annum) from yahoo finance to microsoft excel?
- 11
1
vote
1
answer
1k
views
Excel Add-In Volatility Interpolation I am trying to Understand
The Microsoft Excel at my investment bank has an .xll add-in with a function whose coded functionality I cannot observe. This function is called VolInterp and as the name suggests, calculates the ...
1
vote
1
answer
1k
views
QuantLibXL swap valuation fixing dates
I'm trying to build a Excel spreadsheet with QuantLib 1.4 for swap valuation of "live" swaps; IRSs with a start date in the past, and an end date in the future. This is what I've got so far...
...
- 111
1
vote
1
answer
670
views
Excel XIRR function producing unexpected IRR
I'm using the Excel XIRR function because I have inflows and outflows on the same dates. To use the IRR function instead, I would have to compute the net inflow or outflow on a given date and then ...
- 243
1
vote
1
answer
6k
views
IMM dates in excel
I need to get the IMM dates in excel.
IMM dates are defined as the third Wednesday of every March/June/September/December.
- 319
1
vote
1
answer
146
views
How the variance process in discretised form influence the asset price in the Heston model
I'm trying to do Monte Carlo simulation paths of an asset price with time step $\Delta t$ via the discretised Euler scheme. My main question is how does the variance process influence the asset price ...
- 23
1
vote
1
answer
355
views
How to calculate IRR between 2 numbers
I want to consider 4 scenarios in google sheets. All deal with a periodic return over n periods
Positive to Positive
Positive to Negative
Negative to Negative
...
- 121
1
vote
1
answer
1k
views
Find Interest Rate Swap BUMPs from Bloomberg in Excel
I have a bunch of plain vanilla interest rate swap contracts with all the relevant details regarding payment structure such as notional, fixed rate, index, payment frequency, reset convention, etc.. ...
- 135
1
vote
1
answer
5k
views
Annualising standard deviation (monthly, quarterly data)
The question I have refers to annualised standard deviation.
For example, I have various funds monthly returns data for the period 1980-2019. Some of them report data for e.g. 13, 19, 43, 56 months ...
- 155
1
vote
1
answer
808
views
Multi year performance evaluations [closed]
first question here on StackExchange;
I would value your help, I am on excel working with a 3 year / 36 month investment performance. I am calculating the Sharpe Ratio as follows;
Cell KV32 = ...
- 33
1
vote
1
answer
7k
views
What are Generic government treasury bonds? (Bloomberg terminal)
I have a project at school were we are supposed to find the generic series for US treasury bonds, and then download daily data for 3 years. I have found the bb ticker, but i don't understand the ...
1
vote
0
answers
92
views
Discretisation of Heston SV with Jumps (SVJ - Bates)
I want to simulate a price path of SVJ model (Bates) in Excel to see how it works in real time but I need help on how to discretise and construct the jump part with a Poisson process into Heston model ...
- 23
1
vote
0
answers
1k
views
Bloomberg Excel API for Hourly Volume Data
I'm trying to download hourly trading volume data via the Bloomberg Excel API. For context, this is the formula I'm using, which pulls hourly trading data for Apple between Dec. 10, 2021 and Dec. 17, ...
- 51