Skip to main content
8 votes
Accepted

Complex numbers in VBA

Ahhh complex numbers in Excel VBA, the pinnacle of cubicle programming. Been there, done that! The functions that you quote are indeed returning string types. But ...
Kermittfrog's user avatar
  • 6,957
5 votes

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

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,...
simmy's user avatar
  • 585
5 votes

Excel YIELD function equivalent in python Quantlib

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 ...
Luigi Ballabio's user avatar
5 votes
Accepted

Excel PasteSpecial Values shortcut button causing QuantLib to crash

After digging away, I discovered an issue deep within the base QuantLibXL code: a constructor was not handling an exception. Within the base code that QL uses to wrap the Excel4 C-style interface (...
DS_London's user avatar
  • 223
5 votes
Accepted

Bloomberg bond clean price and accrued amount differs from Quantlib

Several things might be affecting your result. Check if all the conventions are correct, namely the daycount. Regarding, the compounding on the last period, I can replicate Bloomberg's price for that ...
David Duarte's user avatar
  • 5,835
5 votes

Future Value: the functions in Excel, Matlab and Numpy Financial don't work when the number of periods is large

This is not a bug, just how computers work. Would have been better to ask in a non finance forum though. It is called (Integer) Overflow. If you are into reading humorous chats, you can have a look ...
AKdemy's user avatar
  • 9,144
4 votes

Calculating fund alpha using Fama-French 3 factor model?

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, ...
Matthew Gunn's user avatar
  • 6,974
4 votes

Future Value: the functions in Excel, Matlab and Numpy Financial don't work when the number of periods is large

The computer programs are evaluating the following expression: $FV(i,N,PMT,PV)=-PMT[\frac{(1+i)^N-1}{i}]-PV(1+i)^N$ the test case you are running is the special case where you choose $PMT=-i\cdot PV$ ...
nbbo2's user avatar
  • 11.5k
3 votes
Accepted

How to compare bonds in terms of volume traded in Bloomberg Excel Add-in?

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 ...
oronimbus's user avatar
  • 1,906
3 votes
Accepted

Excel Add-In Volatility Interpolation I am trying to Understand

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 \...
Ivan's user avatar
  • 1,396
3 votes

Pulling portfolio from bloomberg

Not sure if this response is useful for OP after 2 years. But in case anyone is searching for solution: ...
ra1n's user avatar
  • 31
3 votes

Holdings based style analysis

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 = \...
lebelinoz's user avatar
  • 274
3 votes

Price at Specific Time from Bloomberg

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 ...
nbbo2's user avatar
  • 11.5k
3 votes

QuantLibXL swap valuation fixing dates

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 ...
Luigi Ballabio's user avatar
3 votes

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

I've posted on my website R code that replicates the Fama-French factors (plus momentum) from scratch. It assumes you have access to WRDS but if you have your own data, you can begin using the code ...
Cyurmt's user avatar
  • 111
3 votes
Accepted

Excel XIRR function producing unexpected IRR

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 =...
Chris Degnen's user avatar
3 votes

How can I get real-time CDOR rate, swaps, and options pricing data refreshed on a daily basis in Excel?

Bloomberg WAPI (same API for all supported languages, including excel vba) refreshes as soon as you send the request. No need to do some awkward refresh of standard ...
AKdemy's user avatar
  • 9,144
3 votes
Accepted

FX Option Price Quotation

You are definitely correct in this example. There is one caveat though. I think that is an error in the book and despite it being defined as a continuous rate, they actually use it like it would not ...
AKdemy's user avatar
  • 9,144
3 votes

Future Value: the functions in Excel, Matlab and Numpy Financial don't work when the number of periods is large

As @noob2 posted, al these libraries do is to apply this formula: $FV(i,N,PMT,PV)=-PMT[\frac{(1+i)^N-1}{i}]-PV(1+i)^N$ However, the same formula can be rewritten as: $-PMT \frac{c}{i} + \frac{PMT}{i} -...
Pythonista anonymous's user avatar
3 votes
Accepted

Is sorting stocks into portfolio mandatory in Fama-French model?

When you only have three stocks in your data set, trying to form portfolios will not be helpful. Run the analysis on the individual stocks' data as is. Using portfolios instead of individual assets in ...
Richard Hardy's user avatar
2 votes
Accepted

Implementation of the Chow test in Excel/R

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 ...
vonjd's user avatar
  • 27.5k
2 votes

Bond characteristics using Bloomberg in Excel

You need to specify that the code is an ISIN: =BDP("DE0001848083 ISIN", "COUNTRY") should work as expected.
assylias's user avatar
  • 955
2 votes

Modeling stock performance in excel

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 ...
Dave Harris's user avatar
  • 4,309
2 votes
Accepted

Bloomberg formula for bond total return

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 ...
oronimbus's user avatar
  • 1,906
2 votes

Excel YIELD function equivalent in python Quantlib

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 ...
user8948's user avatar
  • 151
2 votes

(Bloomberg) BDP Formula question

Just concatenate the string (and replace the _ with spaces to form a valid ticker): ...
assylias's user avatar
  • 955
2 votes

Finding Lead-Lag Relationship

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, ...
user38805's user avatar
2 votes

Single-step Monte Carlo in Excel

The code below is part of a VBA project I did to calculate VaR with Monte Carlo returns. If you eliminate the -1 at the end all values are positive. You just need ...
user89135's user avatar
  • 284
2 votes
Accepted

Populating price data from Bloomberg in Excel with BDH via macro not returning time series

It would be better not to use (old) BDP/BDH, but to use either the COM wrapper (which can be synchronous) or the new BQL or BQL.query calls (HELP BQLX). Some time ago I wrote a a few lines of code (...
Dimitri Vulis's user avatar
2 votes
Accepted

Best way to get Sharpe ratio and volatility based on BBG-data in Excel for automation

As was suggested in a comment in your previous post, BQL would be the way to go, and you should avoid using BDH. Here is a simple examle of the inputs to get a rolling sharpe ratio for whatever ...
David Duarte's user avatar
  • 5,835

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