We’re rewarding the question askers & reputations are being recalculated! Read more.

# Tag Info

## Hot answers tagged excel

12

Yes, you need Cholesky factorization. You can find the general idea here: http://www.goddardconsulting.ca/option-pricing-monte-carlo-basket.html Plus the implementation in MATLAB here: http://www.goddardconsulting.ca/matlab-monte-carlo-assetpaths-corr.html The code in general should be easily translatable. The only difficulty is the Cholesky factorization ...

6

A free to use Excel wizard-based Add-In providing QuantLib-backed derivatives pricing analytics directly in Excel is available at https://www.deriscope.com Since August 18, 2017 Deriscope has moved from beta to production. Disclosure: answerer is author of the package. Update as of 24 Oct 17: Deriscope already covers the whole QuantLib

4

For R see the following packages: http://cran.r-project.org/web/packages/quantmod/index.html http://cran.r-project.org/web/packages/highfrequency/index.html http://cran.r-project.org/web/packages/TFX/index.html http://cran.r-project.org/web/packages/IBrokers/index.html For a broader overview this might help: http://cran.r-project.org/web/views/Finance.html

4

First of all, it is not conceivable to do all that work by hand! You are crazy to have just thought it! Second, if you want to repeat your work with different datasets, I suggest you to use R, since, once you have written a script, you can use it all the times you want. But, there's a 'but': you cannot think we are going to write some code for you (you ...

4

Your question is more or less answered in How to calculate bond yield in QuantLib - Python. Once you've built the fixed-rate bond object (as in the post you linked) you can call fixedRateBond.bondYield(targetPrice, day_count, compounding, frequency) Comparing the above to the Excel interface in your link, targetPrice is pr, frequency is the frequency as ...

4

In the long run, you'd probably be better off learning a real programming language like Python, R, or MATLAB. While you can do this in Excel using mmult, transpose, and minverse, it's rather horrible. In any case, you should know about the mathematical idea of a matrix, matrix multiplication, and the inverse of a matrix. (Multiplying by the inverse of a ...

3

I'm not exactly sure what you're asking, but since you tagged the question as factor-models, I'll assume you are modeling the next month's returns of stocks as a function of the factors: $$r_i = \alpha_i + \beta_1f_{i1} + \cdots + \beta_Kf_{iK} + \epsilon_i$$ where $r_i$ is the next return of stock $i$ $\alpha_i$ is the part of stock $i$'s return ...

3

If you just need the description you can use =BDP(TICKER,"CIE DES") directly in Excel.

3

They are lineary interpolating in total variance. I find the exact same answer as your add-in function returns. In other words the interpolation is made wrt time and between $z_1 = T_1 \times v_1 \times v_1$ and $z_2 = T_2 \times v_2 \times v_2$. $$z_t = \frac{t-T_1}{T_2-T_1} \times (z_2-z_1) + z_1.$$ $$v_t = \sqrt{z_t / t} = 13.5343.$$ Your formula ...

2

Not sure how you're looking to use it, but if you start with an IMM date in cell A1 (e.g. 9/18/2013), in cell A2 put... =DATE(YEAR(A1), MONTH(A1) + 3, 1 + MOD(4 - WEEKDAY(DATE(YEAR(A1), MONTH(A1) + 3, 1)), 7) + 14) ...and drag it down. It will return the 3rd Wednesday of the month for every third month, so assuming that the month of the date you put in ...

2

you need real-time tick in order to achive this. IB, esignal, iqfeed both provides RTD/DDE api to excel integration.

2

Don't be afraid to use one row in your spreadsheet for each day. Enter the date in column A, then increment the date by 1 day for each row until you reach the end of 10 years. In column B enter the formula "=YEAR(A1)" A1 being the cell with the date in it. Then calculate your principal and interest amounts based on the interest rate divided by the total ...

2

In terms of end-user applications, all trading desks and middle office places I know, use either their own proprietary or expensive third party sources. On the other hand there exists a c++ library called QuantLib that is well known among real world practitioners, probably because it contains several routines that are well tested and robust. Often pieces of ...

2

If you make your repayment at the beginning of the month you do not have to pay accrued interest of the amount for the month. So, paying already 961.83$at the first of each month makes a subtle difference to paying the same amount at the end of the month as you have to pay interest on this open position during those 30 days. 2 You can do a Chow test with the strucchange package in R. You can find the full documentation here. The package comes with a vignette which shows how to use it (see p. 9ff. if you want to dive into an example without further ado). I don't know whether there is an appropriate Excel add-in available but in general I would not recommend doing these more ... 2 You need to specify that the code is an ISIN: =BDP("DE0001848083 ISIN", "COUNTRY") should work as expected. 2 BDP() is for current data, to get past data at a specific time or range of times you use BDH() (where H refers to Historical Data) Try for example =BDH("EURGBP Curncy","Last Price","7/13/2017 3:00:00 PM","7/13/2017 3:00:01 PM","BarTp=T,BarSz=1,Fill=P") This will search the time interval from 3:00:00 and 3:00:01 and give you the price of the last trade ... 2 The problem is you are trying to model it with a normal distribution. Prices are data. Returns are not data. Returns are mathematical transformations of raw data. It is mathematically impossible for returns to be normally distributed unless losses are anticipated in every period. Returns can be constructed in four ways. You can either use:$\$\frac{p_{...

2

Going backwards 2 days with qlCalendarAdvance(..., "-2D", ...) works in this particular case. To be more robust, and to take into account the number of fixing days and the conventions of the index you're using, you can use the qlInterestRateIndexFixingDate function. It takes the interest-rate index and a value date (that is, the start of your coupon) and ...

2

You're getting downvoted for asking a bad question. I'll explain why the question is bad. Your link to the Excel documentation has the full specification of what YIELD does. If there is one coupon period or less until redemption, YIELD is calculated as follows: (image here; look at your link) where: A = number of days from the ...

2

There is no field for bonds like you have for stocks to get daily returns in a time series format. However you can try using CUST_TRR_RETURN_HOLDING_PER. This gives you total return over a specific period, you can look up the valid parameters on FLDS (for instance, to adjust the start and end date). Overriding start and end date you can create your own time ...

2

Just concatenate the string (and replace the _ with spaces to form a valid ticker): =BDP("odcn8c " & T2 & " Comdty", "vega") * I68 * 50

2

This is a question the Helpdesk should be more than capable to answer. For Fixed Income generally you need to specify the source since volume data is often not available (exceptions: MIFID 2, TRACE or Exchange data). =BDH("EJ070301 @MFTD Corp","PX_VOLUME","1/1/2010","8/13/2018") Above will give you all MIFID 2 reported trade data YTD. VWAP is also not ...

2

Recently there's been a lot of academic work involving wavelets to detect lead-lag relationship. I'm afraid it is still not nearly as common as the ubiquitous Granger Causality mentioned above. Also, if someone wants to take the discussion more complicated/technical, please go ahead. Who doesn't love arbitrage?

1

If you don't need particularly good accuracy you might try the Whaley approximation. Here's the original paper and an implementation in code. The approximation requires you to run a few numerical iterations to solve a transcendental equation. In practice 10 is always enough so you can just create 10 cells side by side with successive iterations. I ...

1

You can see all the US generic yield indices by typing ALLX USGG into your terminal. This will give the generic indices for multiple maturities. From there just pick the maturities you need. This index is quoted in YTM, so if you want the average yield, you can export the last price series (PX_LAST) from the terminal or pull the series into Excel and take ...

1

You can calculate the monthly IRR then annualise. Both IRR and XIRR = 39.1% pa. Confirming the XIRR, as you calculated in Excel. XIRR = 39.098% pa. You can also calculate the IRR in one step. IRR = 39.062% pa.

1

Dates Index 01.03.2017 100 01.04.2017 110 01.05.2017 115 01.06.2017 117 01.10.2017 116 01.01.2019 120 01.01.2020 121 I took the end points of the months, quarters, and years that you provided; Draw the dot plot with smooth lines in Excel; Right-click on this line and choose "add line trend"; Select "Polynomial" and choose degree "3"; Tick the ...

1

On Bloomberg, you can do this through the TRACE reporting system, which requires dealers to publish prices soon after completing a trade. A summary of recent price and volume activity can be accessed via Bloomberg using the TDH (Trade History) command. However, this doesn't list down all instruments but mainly gives you a way to analyse the market trend. In ...

Only top voted, non community-wiki answers of a minimum length are eligible