Questions tagged [excel]
The excel tag has no usage guidance.
104
questions
-1
votes
0
answers
35
views
How to get bond price changes from a specific date?
I need to get historical change in price data from a bond.
I usually first get the bond price from a specific date (lets say 03.01.2022) and then calculate the cumulative change in price since that ...
- 1
1
vote
0
answers
83
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
0
votes
2
answers
61
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 ...
0
votes
0
answers
65
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
1
vote
1
answer
140
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
0
votes
0
answers
68
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
3
votes
2
answers
484
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
0
votes
0
answers
185
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
1
answer
208
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
85
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
0
answers
99
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 ...
1
vote
2
answers
4k
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
0
votes
1
answer
68
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
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
0
votes
1
answer
158
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
0
answers
195
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.
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
1
vote
3
answers
722
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, ...
0
votes
0
answers
48
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
99
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 ...
2
votes
1
answer
530
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
-3
votes
1
answer
208
views
Issue with Monte Carlo Simulation on an interest rate tree in Excel
I need to build a Monte Carlo simulation model that does 10 iterations on the interest rate tree. I need to identify the interest rate given a sequence of moving up and down the interest rate tree. I ...
- 1
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
4
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 / ...
- 645
0
votes
1
answer
509
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
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
4
votes
1
answer
882
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
0
votes
1
answer
148
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
2
votes
1
answer
193
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
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
1
vote
0
answers
107
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 ...
0
votes
2
answers
285
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
773
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
1
vote
0
answers
183
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 ...
0
votes
1
answer
193
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
1
vote
1
answer
337
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
0
votes
1
answer
313
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:
1
vote
0
answers
137
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
365
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
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
940
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 ...
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
0
votes
1
answer
248
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
2
votes
2
answers
243
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 ...
1
vote
1
answer
4k
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
0
answers
931
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. ...
- 11
0
votes
0
answers
127
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
4
votes
2
answers
577
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