Questions tagged [excel]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer
48 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 ...
user avatar
  • 165
1 vote
0 answers
125 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, ...
user avatar
0 votes
0 answers
35 views

Price stress testing

Good day everyone! I am looking to stress a Future curve of Power Prices to assess the possible future impact on Spot prices. My assumption is that Future prices are suitable to predict Spot prices. I ...
user avatar
  • 1
0 votes
0 answers
212 views

Using the MMULT TRANSPOSE Formula for Ex-Ante Tracking Error

I understand that the most widely used ex-ante tracking error expression is $\sqrt{w^TSw}$. This is very similar to the calculation of Portfolio Variance and/or Standard Deviation. The conceptual ...
user avatar
0 votes
0 answers
55 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 ...
user avatar
  • 1
0 votes
0 answers
67 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.
user avatar
0 votes
0 answers
55 views

Excel for bootstrapping semi-annual bond

I have cash flows with me, and need to calculate the discount factors or zero coupon rates. I know about NPV and XNPV, but I don't have dates, and I'm not sure whether these work for S/A payments. ...
user avatar
  • 1
1 vote
1 answer
472 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 ...
user avatar
  • 13
0 votes
0 answers
67 views

QuantlibXL - How can I define a volatility surface using moneyness instate of strikes?

I wonder if in the function qlBlackVarianceSurface() I can replace the strikes for moneyness and then price the option using the spot = 100% and the option strike = Original_Strike / Original_Spot. ...
user avatar
  • 101
1 vote
3 answers
311 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, ...
user avatar
0 votes
0 answers
41 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 ...
user avatar
0 votes
0 answers
67 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 ...
user avatar
0 votes
1 answer
355 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 ...
user avatar
2 votes
1 answer
164 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 ...
user avatar
  • 219
-3 votes
1 answer
85 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 ...
user avatar
0 votes
1 answer
453 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 ...
user avatar
  • 219
4 votes
1 answer
245 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 / ...
user avatar
  • 553
0 votes
1 answer
261 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 ...
user avatar
0 votes
1 answer
878 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 ...
user avatar
  • 23
4 votes
1 answer
604 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: ...
user avatar
  • 413
0 votes
1 answer
101 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 ...
user avatar
2 votes
1 answer
121 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 ...
user avatar
  • 223
0 votes
1 answer
2k 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 <...
user avatar
  • 219
1 vote
0 answers
45 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 ...
user avatar
0 votes
2 answers
140 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. ...
user avatar
0 votes
0 answers
289 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
user avatar
  • 55
1 vote
0 answers
116 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 ...
user avatar
0 votes
1 answer
85 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 ...
user avatar
1 vote
1 answer
108 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 ...
user avatar
  • 121
0 votes
1 answer
141 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:
user avatar
1 vote
0 answers
40 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 ...
user avatar
1 vote
0 answers
154 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
user avatar
0 votes
0 answers
41 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 ...
user avatar
  • 23
0 votes
0 answers
402 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 ...
user avatar
1 vote
1 answer
632 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.. ...
user avatar
0 votes
1 answer
128 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 ...
user avatar
2 votes
2 answers
190 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 ...
user avatar
1 vote
1 answer
1k 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 ...
user avatar
  • 155
1 vote
0 answers
388 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. ...
user avatar
0 votes
0 answers
65 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 ...
user avatar
  • 199
4 votes
2 answers
342 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.
user avatar
1 vote
0 answers
83 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 ...
user avatar
-1 votes
1 answer
517 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 ...
user avatar
  • 113
1 vote
1 answer
417 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 = ...
user avatar
  • 33
-1 votes
1 answer
576 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? ...
user avatar
0 votes
2 answers
723 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 ...
user avatar
0 votes
1 answer
7k 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 ...
user avatar
  • 11
1 vote
1 answer
228 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 ...
user avatar
  • 13
1 vote
1 answer
803 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 ...
user avatar
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 ...
user avatar
  • 11