Questions tagged [excel]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
2
votes
1answer
83 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 ...
0
votes
1answer
48 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 <...
1
vote
0answers
31 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
2answers
41 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
0answers
47 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
0
votes
0answers
42 views

Calibration of the Ho Lee model in Excel only based on historical treasury rates (3 months)

I would like to calibrate the Ho Lee model $dr_t=θ_tdt+σdW_t$ with $θ(t)= F_t(0,t)+σ^2$ in Excel based on a time series of 3-month treasury rates and then compare the real time series with that ...
0
votes
0answers
69 views

Calibration and comparison of the Vasicek model and Ho-Lee model

I would like to calibrate the Hoo-Lee model and the Vasicek model to a historical interest rate series and compare the interest rate development of both models with the historical interest rate series....
1
vote
0answers
44 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
1answer
39 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
vote
1answer
54 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 ...
0
votes
1answer
73 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
0answers
30 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
0answers
76 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
0answers
35 views

Missing functions in QuantLibXL

I have been using the open source addin QuantLibXL extensively. This provides an easy way to use the QuantLib library in a simple Excel environment. However, there are a few of the more recent ...
0
votes
0answers
32 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 ...
0
votes
0answers
46 views

Unbalanced Panel Data in Excel and Stata

We have unbalanced panel data (columns for date and firm identifier as well as other variables) that is heavily unbalanced involving several thousand firms. We would like to turn it into what ...
0
votes
0answers
108 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
1answer
321 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.. ...
1
vote
0answers
60 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 ...
2
votes
2answers
134 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
1answer
188 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 ...
1
vote
0answers
51 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. ...
0
votes
0answers
43 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 ...
4
votes
2answers
215 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.
1
vote
0answers
71 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 ...
-1
votes
1answer
334 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 ...
1
vote
1answer
312 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 = ...
-1
votes
1answer
464 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? ...
0
votes
2answers
482 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 ...
0
votes
1answer
5k 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 ...
1
vote
0answers
142 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 ...
1
vote
1answer
591 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 ...
0
votes
2answers
1k 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 ...
0
votes
2answers
927 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 ...
1
vote
1answer
738 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 ...
3
votes
2answers
2k 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 ...
1
vote
1answer
580 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... ...
-2
votes
2answers
360 views

Modeling stock performance in excel

I am trying to model the ending value of a stock after a certain number of years, I need it for a bigger project but I made this sample sheet to get help. This sheet is assuming that annual returns ...
0
votes
1answer
19k 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 ...
0
votes
1answer
209 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 ...
4
votes
1answer
418 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 ...
1
vote
1answer
5k 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
0answers
278 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
1answer
517 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 ...
0
votes
1answer
137 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
1answer
1k 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 ...
-1
votes
2answers
654 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
1answer
2k 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 ...
2
votes
1answer
1k 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 ...
0
votes
1answer
8k 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?