There are total 4 major steps in this process and it may seem consume excessive of time initially. However, once the process is in place, you may take less than 5 minutes a day to complete your task.
{For someone is good in Macros, you may help to record the whole procedure and post it back for us. This will make our life easier by just click one “button”, if someone can implant the macros into that “button”}.
Let’s start the journey…
Step 1 is to create a new security for the data to be stored in MetaStock.
- Open the “Downloader” from MetaStock.
- Select “File” from the menu and click “New” and then “Security” in the sub menu.
- The following window will appear:
- The following data need to be provided for the new security to be created:
- Folder – Select your preferred folder for file placement in MetaStock data. For this, I choose Index folder. (My data source is from bizfun.cc; our data is preset in designated folder).
- Name – Insert Oil & Gas, or any preferred title for any new index.
- Symbol – Insert OnG, all securities require a ticker symbol. You may use any symbol for any new index created in future.
- First Date – Insert the first date for your new index. I just constructed this index since April, thus, my first date is 04/01/2009
- Periodicity – Just select “Daily”
- Units – Select “Decimal”
- Optional Fields – Just check ”Opening prices”
- Our newly created security will appear as OnG in the Index folder as shown below:-
Step 2 is to organize the necessary search criteria for our index, namely Open, High, Low, Close and Volume. Also, select the related securities that make up your index.
- Open a new “Explorer” in MetaStock and name it Oil and Gas Index. And, fill up the following:
- Column A = Open
- Column B = High
- Column C = Low
- Column D = Close
- Column E = Volume
- Leave the remaining columns and filter section empty.
- Then click "OK" and you have the scanner for our new index.
- Open “Oil and Gas index” in the Explorer, click on “Explore” and then “Add Securities” button and find the related securities.
- From MTrading folder, you should select Alam, Deleum, Dialog, Epic, Kencana, Oilcorp, Pantech, Penergy, Petdag,
- From MIndProd folder, you should select Coastal, Esso, Knm, PetGas, Scomi, Shell and Waseong.
- Click “OK” and it is saving as “Oil & Gas Index” in Explorer. With this, there are total 20 securities to construct my Oil and Gas Index.
- Then click “Explorer”, select “Oil & Gas Index”
- When the exploration is completed, click in the “Report” to view the result. Then, we should save our Oil and Gas index list in favorite. Detail shown as below:
Step 3 to use the interoperability feature of MetaStock & Excel to calculate the average value of Open, High, Low, Close and Volume for our newly created index.
- Open Excel and create a new worksheet and name this worksheet according to the date. For our case it starts with date 010409.
- Then new will transfer the explore result from MetaStock into this worksheet
- Back to "Explorer" in MetaStock, select “Oil and Gas index” scan and click on “Report”, as below
- Place the cursor on the first security, right click and select “Copy” form the sub menu as shown below.
- Then, back to Excel worksheet. Place the cursor on the top left corner and right click the active cell and select “Paste”.
- In the Excel worksheet, cell in A22, key in 010409, then calculate average value for Open, High, Low, Close and Volume respectively.
- To do the averaging computation, for example for Open data, place cursor at B22 and key in formula =Average (B1:B21). For other average, just drag the cursor from B22 to F22. Result shown as below:-
- The final index result is summarized as below:
- Date = A22 = 010409
- Open = B22 = 2.3085
- High = C22 = 2.3485
- Low = D22 = 2.2745
- Close = E22 = 2.31525
- Volume = F22 = 139781.7
- Place your cursor on Sheet 1 and rename it to 010409. Then, on the same sheet, right click and select “Move or copy” and select the location nest to sheet “010409”. Rename the newly created worksheet of 010409 (1) to 020409. Worksheet 020409 is ready for next day computation. Result as below, you may do the same for the next day.
- Save the Excel file as “Oil and Gas Index”.
Step 4 is to upload the computation result into our index in “Downloader” so that we can view and analyse in MetaStock.
- Open the “Downloader”, select “Open” from the “File” menu. Go to “Index” folder and select “OnG” index. It is shown as below:-
- Open Excel and open “Oil and Gas Index” file, go to row 22, highlight A22 to F22, right click and select copy.
- Back to “Downloader” file, place the cursor on the first row and right click and select paste. As shown below:-
- Once you have pasted all detail as required, select “Save” from the file menu.
- Close the “OnG” index window. The project is complete and “OnG” index is updated.
Conclusion
Once you have everything organized it is just a case of run the exploration and copies the results to your spreadsheet. Providing you have setup you spreadsheet to calculate the average values automatically, this will happen instantly. Note the final values and enter the index values into the “OnG Index” using the “Downloader”. Here is the example of "OnG index" for the month of April 2009.
That’s all, well done.
We will look into the usage of Oil & Gas Index in next sharing.
(All screen shots are courtesy of MetaStock. MetaStock is a registered trademark of Equis Int)
