Questions tagged [excel]
The excel tag has no usage guidance.
104 questions
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
145
views
Excel rate function with nper as decimal returns unexpected result [closed]
I set up a simple problem
Payment after 0.4 year is 25. The rate is 10%. I calculated PV as $\frac{25}{(1+10\%)^{0.4}} = 24.77$
Then I did Rate(0.4,25,-24.77,0) in ...
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
1
answer
506
views
DCF valuation and the constant WACC assumption
I have a question that has been on my mind ever since I learned about DCF. I was taught that for the DCF to be valid WACC should be constant. As a physicist by training this assumption is strange to ...
- 113
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
3
answers
101
views
find the qth lower tail quantile
I have daily currency returns. For each month, I have to find the return associated to the 5% lower tail quantile for each currency (the lowest return or the second lowest return).
Could you please ...
- 119
1
vote
1
answer
594
views
Calculate yield of maturity for a certain price in excel
I have a bond with a time to maturity of 5, a nominal value of $100, coupons of \$3,- and an yield price that I need to calculate so that the bond price equals \$100,-. This yield value is symbolized ...
- 189
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
1
answer
3k
views
Bloomberg bond clean price and accrued amount differs from Quantlib
I'm getting the different bond clean price from Bloomberg and from QL but surprisingly Bloomberg price matches with excel price() function
I have the following bond : GETC21117030. The parameters are ...
- 23
0
votes
2
answers
2k
views
Pulling portfolio from bloomberg
I am not sure wether I should post this question here or on stack overflow, but I'll give it a shot anyway.
I am trying to get my portfolio from PORT in bloomberg. Basically I am trying to automate a ...
- 11
0
votes
1
answer
99
views
Is sorting stocks into portfolio mandatory in Fama-French model?
I am currently doing a report regarding Fama and French 3 and 5 factors model. I was provided 3 companies with each of its daily stock return from 2015-2020, and the values of all 5 factors during ...
0
votes
1
answer
261
views
How can I get real-time CDOR rate, swaps, and options pricing data refreshed on a daily basis in Excel?
I'm currently using "Bloomberg Anywhere", which is the same as Bloomberg Terminal except account-specific rather than PC/hardware-specific. I currently have to refresh the rates every morning at ...
- 39
0
votes
1
answer
71
views
Alpha calculation inconsistent across methodologies
I'm fairly new to finance, and this does not make sense to me.
Consider benchmark & active monthly returns as shown here:
If I do a line of best fit, I get an intercept of 8.4%
Which is meant as ...
- 175
0
votes
1
answer
2k
views
Best way to get Sharpe ratio and volatility based on BBG-data in Excel for automation
For tickerized portfolios in PRTU, I want to extract Sharpe ratios and historic volatilities per annum or for 1, 3, 5 years from Bloomberg. So far, I have been ...
- 209
0
votes
1
answer
5k
views
Populating price data from Bloomberg in Excel with BDH via macro not returning time series
Though issue has been addressed before on stackoverflow and reddit, I was not able to find any useful answers.
I'm running a macro to populate price data with a macro via excel bloomberg API, say
<...
- 209
0
votes
2
answers
1k
views
question on XIRR (excel)
Let's say we have an initial investment of -10 on 1/1/2000, and from 1/1/2001 to 1/1/2018 (with annual payments on Jan-1 of each year for 18 years) we get a CF of +2 each year with a final payment of ...
- 1
0
votes
1
answer
10k
views
Bloomberg formula for bond total return
Do you know a bloomberg excel formula to retrieve the dayly total return of a bond over a period?
By total return I mean : (change in price + coupon)/initial price
Same as for a stock but with ...
- 11
0
votes
1
answer
3k
views
Bond characteristics using Bloomberg in Excel
I would like to retrieve bond characteristics (bond type, coupon type etc.) for corporate bond ISINs using the Bloomberg Excel Addin.
I tried ...
- 11
0
votes
1
answer
65
views
Daily principal payments, accumulated on yearly basis in excel
I am doing something seemingly quite easy: Prinipal calcuation of a loan.
I need to calculate daily principal payments and accumulate it on a yearly basis.
So my current implementation look like ...
- 482
0
votes
1
answer
260
views
Fama-French Regression Output Interpretation (Intercept/Alpha)
I am currently doing a report regarding Fama and French 3 and 5 factors model. I was provided 3 companies with each of its daily stock return from 2015-2020, and the values of all 5 factors during ...
0
votes
1
answer
171
views
Spread duration curve by issuer or by sector
I was surprised to see that spread duration was not offered as a curve in Bloomberg. As a result, I'm trying to find a curve (in Bloomberg) or build a curve (maybe using the Excel API) which ...
- 11
0
votes
1
answer
533
views
IRR with Inflation Rate
I have cash inflows and cash outflows for a 7 year period and my MIRR and inflation rate. That's all the information I have.
How do I calculation the IRR taking the inflation rate into account in ...
- 1
0
votes
1
answer
152
views
How do you simulate returns for a portfolio when you have Lumpsum + Monthly investments (SIP) in place?
I'm trying to simulate portfolio returns using Norm.inv function in excel.
Inputs to the formula: Prob= Rand, Std dev= Historical, Mean= 5 year historical average.
Its easy to do this when you're ...
- 11
0
votes
1
answer
200
views
Different XTIR results between Excel, Google Spreadsheets, LibreOffice Calc and my algorithm
I am developing an algorithm to calculate XIRR, and I found some differences between excel and my algorithm, so I decided to compare other software that have the XIRR formula.
I found the following ...
- 1
0
votes
1
answer
326
views
Swaptions vols,object using quantlib xl
How can I build a good vol surface using QuantlibXl?
My goal is to price a swaption 5 year with option maturity 1Y1M.
The data are:
0
votes
2
answers
2k
views
Calculating 10-year Sharpe ratio for a mutual fund in excel?
Probably a very simple question but here goes.
I am looking to calculate the Sharpe ratio for some funds in excel (173 funds to be exact). The monthly returns I have are from January 2006 to December ...
- 21
0
votes
1
answer
351
views
How do I modify my basic black scholes model in Excel to price american options?
I've modeled a basic black scholes model in Excel and I have been using it to price European options for backtesting purposes.
This has been working fantastically and I would like to adjust this to ...
- 31
0
votes
1
answer
227
views
cubic spline in excel with month, quarter and year inputs
Using Excel, how could I calculate a cubic spline curve in monthly granularity when my inputs are a combination of months, quarters and years? The quarter and yearly averages of the spline curve need ...
0
votes
1
answer
10k
views
how to convert quarterly data to monthly
Is there any way to convert quarterly data to monthly in excel or preferably in STATA?
I
Next to that, how can I transform dates in excel so as to be recognized by STATA?
- 53
0
votes
2
answers
2k
views
How to automatically get all options data for a particular stock into microsoft excel?
I'm looking for a way to get the entire options chain (All options expiries) for a particular stock in excel without manually copy pasting anything. It does not have to be real time and I will only be ...
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
1
answer
2k
views
Bloomberg Excel API, how to extract one number out of BDS
The BDS function provided by the Bloomberg Excel plugin produces multiple cells. For example, =BDS("VOD LN Equity", "TICK_SIZE_TABLE") fills three columns and 19 rows of data. The ...
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
708
views
How to compare bonds in terms of volume traded in Bloomberg Excel Add-in?
I want to compare how much bonds of a handful of companies have been traded over the last years and pick the most traded bond for each company via the Excel add-in.
One example of what I have tried ...
- 133
-1
votes
1
answer
981
views
(Bloomberg) BDP Formula question [closed]
Good Morning,
I am using the below formula and need some help, how do I change the below strike price (400) to pull from a separate cell (T2) I have on the spreadsheet?
...
- 1
-1
votes
1
answer
44
views
Why is it cheaper to repay monthly loan at the start of the month [closed]
Assuming I borrow $50000 from the bank where the details are as follow
...
- 99
-1
votes
2
answers
1k
views
Extract Corporate Bond volume
Is it possible to extract the volume of Corporate bonds in Bloomberg with BDP, BDH or BDS function ? Example : 958254AC8 Corp; 98389BAM2 Corp; 94974BFP0 Corp. Doesn't seem like it's possible, even if ...
-1
votes
1
answer
3k
views
Forward 12m EPS Growth and its Earning Revision using Bloomberg
I'm currently trying to figure out how to use data from Bloomberg to create some analysis on a company's performance using Excel/R.
How can I find the Forward 12m EPS Growth, its 1m Earning Revision ...
- 129