Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [excel]

The tag has no usage guidance.

1
vote
1answer
53 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
21 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
30 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 ...
3
votes
2answers
76 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
43 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
78 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 ...
0
votes
1answer
80 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
219 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
232 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
2k 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
82 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
312 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
1answer
677 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
516 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 ...
0
votes
1answer
401 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 ...
2
votes
2answers
864 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
300 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... ...
-3
votes
2answers
295 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
10k 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
150 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 ...
3
votes
1answer
127 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
3k 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
98 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
427 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
104 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
443 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
316 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
1k 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 ...
1
vote
1answer
884 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
5k 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?
-1
votes
1answer
600 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 ...
2
votes
0answers
392 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 ...
5
votes
2answers
8k views

Could someone teach me how to construct the portfolios by compute (like using R, Excel or Eviews)

Recently, I am doing my dissertation that covers asset pricing theory. The empirical test of Fama 3 factors model is an important part of this dissertation. Please let me review the fama model. Fama ...
1
vote
0answers
41 views

Deming Regression

I am trying to test the linearity = interdependence or the non-linear (contagion) between Asian countries during the Asian crises using the fluctuation of the exchange rate. Is it relevant to use the ...
1
vote
0answers
331 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. ...
1
vote
3answers
57 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 ...
1
vote
0answers
947 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 (...
3
votes
3answers
2k views

Step By Step method to calculating VaR using MonteCarlo Simulations

In trying to find VaR for 5 financial assets with prices over a long period of time(2000 days worth of data) how would I do the following: Carry out monte-carlo simulation in order to find a VaR ...
-1
votes
1answer
32 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 ...
0
votes
2answers
1k 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 ...
1
vote
1answer
394 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 ...
3
votes
1answer
654 views

Export security description data from bloomberg into excel

I have the cusip of about 300 bonds. I want to retrieve the security description from bloomberg and then export it into excel. Does anyone have any tips as to how to accomplish this?
2
votes
1answer
250 views

excel yearfrac 30u/360 across feb month end

Why are both of these formulas returning 1? One of them should be 3. =YEARFRAC(DATE(2015,2,27),DATE(2015,2,28),0)*360 =YEARFRAC(DATE(2015,2,28),DATE(2015,3,1),0)*360 Based on every description of ...
0
votes
1answer
40 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 ...
2
votes
2answers
8k views

Bloomberg Libor Rates

I load swap rates data from Bloomberg in excel using the command =BDH(Ticker,"Px Last",Start_Date,End_Date,"Dts",FALSE) The tickers I use are "USSW1 Curncy", "...
4
votes
1answer
2k views

Portfolio Optimization with Monte Carlo Simulation - How to do it with Excel?

If I have three asset classes and their historical weekly returns for five years, how can I construct a minimum variance portfolio and an efficient frontier plot with Excel? To do that do I have to ...
4
votes
1answer
743 views

Obtaining logical lists of Bloomberg security codes in Excel

I am using Bloomberg's BDP and BDH functions in excel to retrieve data for a set of options. The problem is that (as underlying prices move and expiration dates come and go) option strikes are ...
1
vote
0answers
111 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/...
1
vote
0answers
2k 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 ...
2
votes
1answer
2k views

How to fully replicate ADX + DI Indicators in Excel? [closed]

For black box testing, I was hoping that I could replicate the ADX + DI+ and DI- indicators that are provided in trading platforms such as ThinkOrSwim, ScottradeElite etc. However, I noticed that ...