• Australian (ASX) Stock Market Forum

Hello and welcome to Aussie Stock Forums!

To gain full access you must register. Registration is free and takes only a few seconds to complete.

Already a member? Log in here.

Approximating ASX Historical Constituents List using Metastock Database

Discussion in 'Software and Data' started by ajcode, Aug 4, 2016.

  1. ajcode

    ajcode

    Posts:
    137
    Likes Received:
    19
    Joined:
    Feb 12, 2014
    Time for a new thread :).

    This time I have written a DLL that ranks ASX securities by previous 6 month median daily turnover (daily turnover = close x volume) and writes the rank to the securities Open Interest field of a MetaStock database for the duration of the current period. The current period or Re-balance can be Monthly, Quarterly or Half Yearly (March/September). So at the close of the 3rd Friday of the month of the start of the current period in question, I calculate the median daily turnover for all found 3 letter ASX securities over the previous 6 months. Rank/Sort the securities based on this value, then write the ranking to the securities Metastock database Open Interest field for the duration of the current period/Re-balance.

    Note: To proceed any further, you need to have either Perl or Python installed and have a Metastock database with ASX securities.

    So after downloading the attached files (later post), if you have 64 bit perl/python installed, unzip 64 bit dll, otherwise unzip 32 bit dll. The directory structure should look like this (with dll in same folder as scripts):

    MSDRK_01_01.png

    Since we need to write to your Metastock Database, it's probably a good idea to create a copy of it just in case I stuffed up :banghead:. For example if your Metastock files are stored in Trading Data, simply copy to Trading Data2.

    Next step is to tell my dll where your copied Metastock Listed and Delisted securities are stored by editing either perl or python scripts in notepad like so:

    Special Note: If you don't have a Delisted securities path or don't want to include Delisted securities, change value to "" in script.

    MSDRK_01_02.png

    Now all we need to do is run either script (not both) from the command prompt. When you do, you will be asked a couple of questions.

    Re-balance method effects how often we do the 6 month calculations from the 3rd Friday of the starting period's month.

    Process Last 2 Re-balances only. If we answer yes, then we only update Metastock database for the 2 most current periods otherwise we calculate from earliest found date.

    Minimum Trading Days means if a security traded less than this amount of days in the previous 6 month period, a value of 0 rank will be written to the Metastock database indicating that security was not included in the constituents list for that Re-balance period.

    Write to MS files. If we answer yes, we will update the Metastock database otherwise we don't. In either case, the constituents list is written to a log file.

    Below is an example of what happens at the command prompt when we run either script:

    MSDRK_01_03.png
     
  2. ajcode

    ajcode

    Posts:
    137
    Likes Received:
    19
    Joined:
    Feb 12, 2014
    Below is some log file entries generated from either script run in last post when viewed from a spreadsheet application. This basically shows the constituents list for each Re-balance period. Probably best to sort by Column A(Rebal_#) Ascending then by Column D(Pos/Rank) Ascending to get the ordered list of constituents for each period.

    Column A contains the Re-balance number starting from period 1 where database starts.

    Column B contains the Database, which is either Listed securities or Delisted securities.

    Column C contains the security's 3 letter code.

    Column D contains the rank of where the security stands when compared with Median Daily Turnover. This value is written to the Open Interest field of the Metastock database.

    Column E contains the Total number of securities included in the constituents list for that particular Re-balance period.

    Column F contains the Median Daily Turnover of that security which is used in the ranking process.

    Column G contains the Total of all Median Daily Turnovers for all securities included in the constituents list for a particular Rebalance period.

    Column H contains the Percentage value of securities Median Daily Turnover divided by Total Median Daily Turnover (Col F/Col G) * 100.

    Column I contains the start date (inclusive) of where rank was written to the Metastock Database.

    Column J contains the end date (inclusive) of where rank was written to the Metastock Database.

    Column K contains the date of where Median Daily Turnover calculations start from (inclusive).

    Column L contains the date of where the Median Daily Turnover calculations end (inclusive).

    MSDRK_02_01.png

    I have attached the following files:

    DLLMSDRK32_20160802.zip - Contains 32 bit dll
    DLLMSDRK64_20160802.zip - Contains 64 bit dll
    msdrk.pl.txt
    msdrk.py.txt

    MSDRK stands for MetaStock Data Ranker :)

    Please remove .txt extension from downloaded scripts.

    Now all you need to do is check changes with an application that reads Metastock files :). Please feel free to add any comments/suggestions/bugs/improvements :xyxthumbs.

    Cheers,

    Andrew.
     

    Attached Files:

  3. skyQuake

    skyQuake

    Posts:
    2,904
    Likes Received:
    45
    Joined:
    May 1, 2007
Loading...

Share This Page