Creating a Custom Index - From ASCII files
Author: wycan
Creation Date: 2/15/2009 9:00 AM
profile picture

wycan

#1
Eugene, Cone,

I have several years worth of daily ascii files each of which contain only a selection of stocks that were created using another strategy. I would like to take these stocks each day and calculate an average close. So, at the end of each day I would only have one average close. The trick also is that I would need to include the close of any stock that dropped off the list from the previous day. I would like to be able to plot all the average closes on the price pane and have the ability to drag and drop indicators on top of this index. I've searched thru the site and have seen some of your suggestions with using DataSeries and GetExternalSymbol for an index etc.. so i have a couple of ideas.. but would like your input.

My question is how would either of you approach this ? thx.
profile picture

Cone

#2
QUOTE:
I would like to be able to plot all the average closes on the price pane and have the ability to drag and drop indicators on top of this index.
In order to use the PricePane for this purpose, the only reasonable way is to create an "index symbol", in the same way that Index-Lab works. Until Index-Lab is integrated with v5, an ASCII-based solution is the easiest. In other words, create the average and output to an ASCII file. Create an ASCII DataSet pointing to your index symbol's directory.

QUOTE:
The trick also is that I would need to include the close of any stock that dropped off the list from the previous day.
This requirement isn't clear to me. If it dropped off the list, why would still include it? You'll have to clear this up (simple examples help) before I can answer further.

profile picture

wycan

#3
thx Cone.. pls see below

QUOTE:

In order to use the PricePane for this purpose, the only reasonable way is to create an "index symbol", in the same way that Index-Lab works. Until Index-Lab is integrated with v5, an ASCII-based solution is the easiest. In other words, create the average and output to an ASCII file. Create an ASCII DataSet pointing to your index symbol's directory.


I think what you are suggesting here is to create one consolidated ascii file that contains date and avg close. ?
ex:
2/12/2009 34.36
2/13/2009 35.40
2/14/2009 37.30
etc...
Then import that as an ascii file from the data manager and assign it a "index symbol" ? Or if there is a link I could learn about this, just point me to it etc..


QUOTE:

The trick also is that I would need to include the close of any stock that dropped off the list from the previous day.

This requirement isn't clear to me. If it dropped off the list, why would still include it? You'll have to clear this up (simple examples help) before I can answer further.


The example would be, let's assume there are two ascii files;
file_2_10_2009
Stock A
Stock B
Stock C
Stock D

Avg Close = (Close[A] + Close[B] + Close[C] + Close[D] )/4 ;

file_2_11_2009
Stock A
Stock B
Stock D
Stock F
Stock G

Avg Close = (Close[A] + Close[B] + Close[C] + Close[D] + Close[F] + Close[G])/6;

In other words, Stock C dropped off the list on 2/11/2009 but the close is included only for 2/11/2009.
On 2/12/2009 (the next day) Stock C Close would not be included unless it reappeared on the list again.

The reasoning for including it, is that if there is high rotation from stocks each day, that's not good. It suggests stocks are meeting then dropping of the list and being replaced by other stocks. If all you did was focus on the stocks each day (disregarding which ones dropped off), you would not get a true measure of how the index is fairing from day to day. By including the close of the stock that dropped (for that one day), you are forcing the index to a truer picture of what's happening. At least that's the theory.. but since I haven't created this yet, I don't know for sure :)

If the answer to the first part of this is creating one consolidated ascii file then I can handle the second part of this issue fairly easily.


Does that help ? thx
profile picture

Cone

#4
If stocks are exiting and reentering the list, what method do you have to indicate their status of being in our out of the list?
profile picture

wycan

#5
QUOTE:


If stocks are exiting and reentering the list, what method do you have to indicate their status of being in our out of the list?


The lists (ascii files) are generated by another program so each file is independent of one another. There is no method at this point that would indicate which stocks are entered or dropped other than comparing the files. I think I know how to approach creating the index, based on your description on creating one ascii file with the closes and importing it. I can calculate the close of the dropped stocks by comparing the delta between the files. I believe this has put me on the right track. thx for your help.