The issue for duplicated dates on low trading stocks in Eikon Excel add-in
Often we need not only stock prices, but also dividend and splits -including total returns.
In Thomson Reuters Formula builder we add the stock(s) of interest and the data varieble that we want to retrieve for that particular stock.
As output the formula builder yields a formula and retrieves the data. For example the following asks for daily total returns for the RIC identifier EABG.HE for a defined data range.
=TR(“EABG.HE”;”ZAV(TR.TotalReturn1D)”;”Frq=D SDate=2018-01-31 EDate=-1W CH=Fd RH=IN;date”;AA25)
However there is an issue with the output for low traded stocks. Zero returns for any given day can result through two alternative scenarios: 1) todays close price ending up equal to closing price from the previous day or 2) no trading.
If the stock is traded and the close price equals to previous date, output will be zero.
In case there is no trading, the output will duplicate date and return.
Let us study a part of the output from the previously shown data retrieval. Total return for 6th of February 2018 for EABG.HE was -4.35.
On 7th of February stocks of EAB group where traded, but the closing price ended up to be the same as from previous day. Thus total return is zero.
No trading on EAB.HE occurred on thursday 8th of February, nor friday 9th of February, nor monday 12th of February, nor tuesday 13th of February, nor wednesday 14th February, nor thursday 15th of February, nor friday 16th of February.
In total we have 7 trading days with no trading of EAB.HE. This shows as seven duplicates in data output.
EABG.HE 6.2.2018 -4.34783
EABG.HE 7.2.2018 0
EABG.HE 7.2.2018 0
EABG.HE 7.2.2018 0
EABG.HE 7.2.2018 0
EABG.HE 7.2.2018 0
EABG.HE 7.2.2018 0
EABG.HE 7.2.2018 0
EABG.HE 7.2.2018 0
EABG.HE 19.2.2018 0
EABG.HE 19.2.2018 0
EABG.HE 19.2.2018 0
EABG.HE 19.2.2018 0
EABG.HE 19.2.2018 0
EABG.HE 26.2.2018 -4.54545
EABG.HE 26.2.2018 -4.54545
EABG.HE 28.2.2018 -2.85714
The next day, monday 19th of February, trading occurs, but closing price remains intact. Thus we get new date, but zero returns. The four remaining trading days of that week include no trading of EABG.HE and because of this there are four replicate lines.
Next trading day is monday 26th of February. Now we have negative return of -4.55. On tuesday 27th there is no trading of EABG.HE. On wednesday 28th there is trading with altered closing price and thus we get a return for this day .
Responses