Using ValueWhen with Foreign for calling the last available value

[jptrader]

Hi!

I would appreciate it if someone could help me with the following doubt.

I have stored fundamental data in symbols named after the ticker of the company (for example, annual ROE value for MSFT would be "MSFT_AN_ROE") in order to call that data by a Foreign function.

ROE = Foreign(Name() + "_AN_ROE", "Volume");

That symbol has one input per year, depending on the publication of the data (for this example, that would be 30/06/2008)

I would need to call that value during the whole year up to the publication of the following one. In order to make all the following bars replicate that value, I have used ValueWhen.

ROE = Foreign(Name() + "_AN_ROE", "Volume");
LastROE = ValueWhen(NOT IsNull(ROE), ROE);
Filter = 1;
AddColumn(LastROE, "ROE");

However, it is not working how intended. Array "LastROE" is completely empty...

I am unable to get what I am doing wrong. Any idea?

Thanks for your help.

Regards.

[Tomasz]

Please display the array BEFORE it gets to ValueWhen, i.e.

AddColumn( ROE, "ROE");

so you know what you are inputting into ValueWhen

[mradtke]

Also, it would help if you used the same symbol for your "not working" example. In the original post, you showed data for MSFT and then for AAPL.

[jptrader]

Tomasz, mradtke, thank you for your prompt answer.

Taking the example for MSFT, the annual ROE symbol would be MSFT_AN_ROE and contains the following data:

I have updated the code as Tomasz suggested

ROE = Foreign(Name() + "_AN_ROE", "Volume");
LastROE = ValueWhen(NOT IsNull(ROE), ROE);
Filter = 1;
AddColumn(ROE, "ROE");
AddColumn(LastROE, "LastROE");

and got the following result. I am unable to make the code keep the last available ROE value for LastROE.

What am I doing wrong?

[Tomasz]

Please use "V" not "Volume" in Foreign call. Also see @mradtke solution below.

[mradtke]

You're expecting Null values in ROE, but there aren't any, there are just zeroes. So try this instead:

LastROE = ValueWhen(ROE != 0, ROE);

Of course, if 0 is a valid value for ROE, then you'll need to come up with a better way to manage your data, because the line of code above will basically ignore values of 0.

I believe you could also take a simplified approach and just use:

ROE = Foreign(Name() + "_AN_ROE", "V", 2);

This will cause AmiBroker to fill in missing values with the previous value, as described in the documentation for Foreign().

[jptrader]

Definitively, this is a much better aproach

ROE = Foreign(Name() + "_AN_ROE", "V", 2);

Thank you both for your help!