Removing the #N/A N/A value of a column with a blank cell

[masa123]

Hi,

I'm using the Bloomberg Excel Addon and trying to figure out on how to remove the #N/A N/A values from a BDH (Bloomberg Data History) function, which is a function that refers to a single cell but will give me the price history from a specified range i.e 4/12/2012 to 18/12/2013. Due to the fact that some of the securities have data history starting later than the specified start date, I'm getting #N/A N/A values when the data is not available and this messes up with my calculations. I would need to change the #N/A N/A values to a blank values instead.

As I'm downloading the price history of several hundreds of components, and the excel spreadsheets are getting really large, I would want to avoid creating unnecessary check cells.

I could get around my problem by creating an extra vector for each component where I have the original column, and on the right side I have a cell that checks whether the cell is #N/A N/A. i.e in B4 I have:

=BDH("security name";"PX_LAST";"12.4.2012";"18.12.2013";"Days=A";"Dts=H";"Fill=P";"FX=EUR";"UseDPDF=N";"CshAdjNormal=n";"CshAdjAbnormal=n";"CapChg=n";"cols=1;rows=615").

This will give me the price on day 12.4.2012 on B4, 13.4.2012 on B5 and so on.

For C4 I could type

=IF(B4="#N/A N/A";"";B4)

However, this would have to be created for every cell (C5, C6, C7, ...) , for a long price history, and for several hundreds of securities creating tens of thousands of checks to the excel spreadsheet. So I'm wondering if the original formula could be adjusted to keep my sheet lighter?

[martindwilson]

if you are getting excel #N/A errors the just use iferror
iferror(yourformula,"")

[masa123]

Hey Martin. Thanks for the suggestion. This does not however help with the problem as Excel treats the "#N/A N/A" as string of text instead of an error. I also tried the following on the original formula:

=if(BDH("security_name";"PX_LAST";"12.4.2012";"18.12.2013";"Days=A";"Dts=H";"Fill=P";"FX=EUR";"UseDPDF=N";"CshAdjNormal=n";"CshAdjAbnormal=n";"CapChg=n";"cols=1;rows=615")="#N/A N/A";"";BDH("security_name";"PX_LAST";"12.4.2012";"18.12.2013";"Days=A";"Dts=H";"Fill=P";"FX=EUR";"UseDPDF=N";"CshAdjNormal=n";"CshAdjAbnormal=n";"CapChg=n";"cols=1;rows=615"))

This works for the first cell of a column, but the rest of the values will be #N/A N/A until the price history begins.

[Pepe Le Mokko]

Never heard of a string "#N/A N/A" when a value is unavailable? ( or is this specific to the addon?)

[masa123]

You're right, it's due to the Bloomberg specific Addon

[martindwilson]

what about
=substitute(your formula,"#N/A N/A","")
its hard to tell as i dont have your addin or see your workbook
or maybe

=iferror(substitute(your formula,"#N/A N/A",na()),"")

[masa123]

what about
=substitute(your formula,"#N/A N/A","")
its hard to tell as i dont have your addin or see your workbook
or maybe

=iferror(substitute(your formula,"#N/A N/A",na()),"")

Martin the suggestions ultimately do the same thing as my version (if(my_formula="#N/A N/A","",my_formula) so it helps with the first cell, however the cells below are still #N/A N/A.

[martindwilson]

isn't there an option to turn off show #N/A NA for non trading days in the options of the place where you get the data from?

[martindwilson]

you need to do it to all formulas that return #N/A N/A"
or is the #N/A N/A" just put in by vba?

[masa123]

you need to do it to all formulas that return #N/A N/A"
or is the #N/A N/A" just put in by vba?

Let me try to explain further: The way it works is I have the formula in cell B4, it will get the price to the cell on the start date (4/12/2012). In cell B5 it will get me the price of 5/12/2013 and this happens until we reach the end date (18/12/2013). The formula itself is only written on the first Cell (B4) and the cells below are filled automatically. Now when we try to adjust the formula with commands such as if the formula returns the value #N/A N/A, change the #N/A N/A to a blank cell, it corrects the first cell, however not the cells below that are automatically filled. I would be glad to provide you with an example sheet, however, the Addin has to be installed on the computer otherwise no data is returned.

[martindwilson]

where does the source data come from then?
cant you just use find/replace

[masa123]

The source data comes from the Bloomberg Excel Addon which will get the values from the Bloomberg terminal.

[martindwilson]

according to google the bloomberg history wizard has the option to turn on/turn off #N/A N/A", but ive no idea how any of that works