Questions tagged [excel]
The excel tag has no usage guidance.
28 questions with no upvoted or accepted answers
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
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
0
answers
93
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
1
vote
0
answers
110
views
Portfolio Weight Constraints
Hi,
So I was asked to calculate the maximum sharpe ratio using solver in excel but it gave me really big numbers for the portfolio weights. So I was wondering what is a good constraint to add and why ...
1
vote
0
answers
190
views
Option Based Portfolio Insurance OPBI Simulation Excel
I want to simulate an example of OBPI in Excel. I can't find any example online with random figures that show how to simulate it.
I tried to understand the appendix of Perold (1995): Dynamic ...
1
vote
0
answers
382
views
How to use quantlib Excel for valuation of european swaption with vol surface to calibrate?
I would like to replicate the following Bloomberg pricer in quantlib using Quantlib xl. Is it possible?
Thanks
1
vote
0
answers
505
views
how to use excel solver to get the shares weights minimize a risk indicator given a fixed profitability I want to make?
I don't know whether this question should have better been asked on Quantitative Finance's forum or Cross Validated or Super-User.
I want to minimize a risk indicator given a fixed profitability I ...
1
vote
0
answers
3k
views
Exporting Time Series Data For Securities Prices From Bloomberg to Excel
I have a list of securities over a thousand entries long that I want to construct a time series of prices for over a specified historical period (e.g. 2/01/10-2/20/10). Doing this manually would take ...
- 632
1
vote
0
answers
126
views
Clarifying the way in counting number of weeks for calculating historical weekly volatility in Bloomberg
I would like to confirm the way to counting number of weeks for calculating historical weekly volatility in Bloomberg. Given an option with issue date from 14/1/2014 to 13/1/2019, is the proper way to ...
- 501
0
votes
0
answers
74
views
Heston model: odd simulations of variance and asset price process path
I've done Monte Carlo simulations of asset and variance processes of the Heston model on Silver via a Full Truncation of Euler discretisation scheme to learn and see for myself how the simulation ...
- 23
0
votes
0
answers
69
views
A TIPS that matures in less than a month from now should trade like a treasury...Why can't I get the YTM's to match?
If a TIPS bond matures soon, lets say 4/15/24...we should know all the payments. I would think that if we calculate the YTM on that after adjusting for inflation it should have the same YTM as a 4/15/...
- 119
0
votes
0
answers
226
views
Pricing a floating rate callable bond with rate scenarios, please help!
I need to price a floating rate callable bond in Excel. I am new to this and struggling to find good information on this specific situation.
I have several rate scenarios until maturity, i.e. the ...
- 1
0
votes
0
answers
102
views
negative portfolio variance? Creating a positive semi definite matrix in excel
I am attempting a portfolio optimization model and ended up generating negative portfolio variance using 2WaWbσaσbcorrel(a,b) or 2WaWb*Cov(a,b)
From reading the linked article where other users had an ...
0
votes
0
answers
207
views
Does anyone have all of Paul Wilmott's "Spreadsheets and VBA" files?
I couldn't find it on his website and the only ones I have are the files contained in the Introduction to Quantitative Finance's CD.
0
votes
0
answers
51
views
Where to find Historical price data (csv preferable) for global Gov bonds and markets. All dollar denominated?
I'm doing a lengthy backtest on a certain portfolio and i've managed to get FTSE 100 data going back to 05-30-1990, where could I find data for global Government bonds going back to that date?
A csv ...
0
votes
0
answers
102
views
Backtesting a permanent portfolio
I'm looking to backtest this portfolio: Global Bonds, gold, Global Stocks, short-term t-bills (1/4 each) from 1990 up to this year, rebalanced monthly. Then take a variety of statistics on the time ...
0
votes
2
answers
312
views
free equity screeners with export to excel
I am having a tough time finding a free equity screener that allows me to download the following data into excel: P/E or EV/EBITDA, Growth, Return on capital or any return measures would be helpful. ...
0
votes
0
answers
825
views
How to Calibrate SABR parameters by fixing Beta(Quantlib XL/Python)
Unable to find a good example for either Quantlib XL/Python where we can calibrate Alpha, Rho and Nu by fixing Beta. Any help would be appreciated
- 55
0
votes
0
answers
43
views
how to calculate yearly volatility from weekly obersvations over 179 weeks?
I am working right now at something and I want to get sure that I am not doing any mistakes - maybe you can help me:
I collected weekly returns from a stock over 179 weeks and know I want to ...
- 23
0
votes
0
answers
999
views
Excel formula for Laplace distribution
I am trying to create a forecast model, projecting the number of passengers through an airport over a period of time (daily, weekly, and monthly). I've already used Excel's FORECAST and POISSON ...
0
votes
0
answers
133
views
Calculating the interest portion of a loan between two dates
The IPMT function in Excel calculates the interest portion of a loan between 2 dates. Is this a closed loop formula? Or are the payments projected and the interest summed up?
I need to implement ...
- 199
-1
votes
1
answer
1k
views
Swaption Corridor Payoff Diagram
What does the payoff diagram look like for a long payer swaption corridor?
For example, suppose that I am looking at a long-payer $1 \times 10$-year swaption with 10Y swaps as the underlying. If I ...
- 223
-1
votes
2
answers
76
views
using a normalized formula from a book but not getting the correct values
I'm attempting a normalization formula (seen in the picture) but I'm not getting the result of 0 and 1. Instead I'm getting values greater than 1 and less than 0 (seen in the other picture).
I wrote ...
