Microsoft excel as tool for stock trading

[robert]

[Lorbie]

pag sa PSE website pwede mo i-copy paste yung historical datas pero di pwede gamitin mga excel functions, hindi ata numeric yun kasi.. sa citisec okey kumuha nang data pero walang historical..

yun lang.. :p

[mcgrath]

mga sirs,

question. how do you get data files for metastock. sino magaling dito sa metastock baka pwede magtutorial.

[OnIn]

uunga mga sirs!... baka pwedeng magseminar ang mga beterano hehe..
penge na rin po historical data ah haha!
_________________
To infinity!.. and beyond!

[serval]

PSE historical data is available from COL download site, their data goes back to 1985 for some stocks. It's in metastock format. Use the following utilities to convert it into csv format. http://www.geocities.com/mimansoor/MsCmdLineUtils.html

I think historical data is no longer a hard to find item that no one wants to share like years ago. Not so long ago, those who have it would charge you to give you a copy. With the advent of the internet, you can find almost anything, even stock market ebooks, without charge.

[glennba27]

[serval]

Once you logged into COL, on the third row of the menus, you'll see "Downloads". If you can't see it, use the find utility of your browser. Click that and you'll be given another page. This is where you can download the PSE data, the Citiseconline toolkit and the COL seminar presentations. The PSE historical data is under the heading "Download Stock Price Data." These are in LZH format, a compression format like ZIP. Just use any compression utility like Winzip or Izarc to extract the files.

COL updates these files every day and I notice they adjust the stocks also for dividends. After you get the initial data, you can use Yeahbah's pseget utility to update your data daily. (http://www.absolutetraders.com/yeahbah/downloads.php) (Don't forget to donate, it's just like treating the authro to a snack or dinner.) Afterwards, you also have to adjust the data yourself when individual stocks get stock dividends.

Here's what I use to convert the metastock data into csv format. Using the "mimansoor" utilities mentioned previously, here's how I convert the data into csv format. Just make a batch file like b.bat and put the following entries.


ms2asc -m MASTER -f financia.csv -i .\financia --ignoreTicker=yes --ignorePer=yes --ignoreTime=yes --ignoreOpenInt=yes
ms2asc -m MASTER -f holding.csv -i .\holding --ignoreTicker=yes --ignorePer=yes --ignoreTime=yes --ignoreOpenInt=yes
ms2asc -m MASTER -f index.csv -i .\index --ignoreTicker=yes --ignorePer=yes --ignoreTime=yes --ignoreOpenInt=yes
ms2asc -m MASTER -f industri.csv -i .\industri --ignoreTicker=yes --ignorePer=yes --ignoreTime=yes --ignoreOpenInt=yes
ms2asc -m MASTER -f prop.csv -i .\prop --ignoreTicker=yes --ignorePer=yes --ignoreTime=yes --ignoreOpenInt=yes
ms2asc -m MASTER -f services.csv -i .\services --ignoreTicker=yes --ignorePer=yes --ignoreTime=yes --ignoreOpenInt=yes
ms2asc -m MASTER -f mining.csv -i .\mining --ignoreTicker=yes --ignorePer=yes --ignoreTime=yes --ignoreOpenInt=yes

Using Winzip or Izarc, extract the LZH files in a temporary directory, for example, c:\temp. Put the utilities and the b.bat batch file in that directory. Using explorer, double click on the b.bat file and the conversion will start. You can also use a command prompt to run the batch file. You'll get the following files:
services.csv
prop.csv
mining.csv
industri.csv
index.csv
holding.csv
financia.csv

You can use your charting software to import those files. If your charting software can directly read metastock data, you can do that instead of converting the data to csv first. In the COL PSE data, I noticed MINING.LZH and MINING&O.LZH are just the same, I just use MINING.LZH when converting the data.

[glennba27]

Alright! Thanks po sa tutorial! Yun pala yung LZH files.

[OnIn]

sa wakas, was able to download the historical data from Citisec.. uploaded na yung data ko sa metastock

kaso paano po mauupdate yung data using naman po sir boypunas', yeahbah's pseget masterpiece? para naman po d na ko magDL ng files from citisec?

thanks!
_________________
To infinity!.. and beyond!

[StreetTrader_13]

thanks sir robert... ngayon ko lang nakita itong thread, limited na kasi access ko sa internet since lumipat na ulit ako dito sa manila... sa likod kasi ng pwesto ko nakaupo ang boss ko...hehehe..

anyways, i'll post my share later... later ko ipost ang link how/where to download data to get intraday prices at yehey & pse using microsoft excel...
_________________
kikoy's LATEST TRADINGCALC with autoUpdate - FASTER Download
______________
latest update as of Sept 26, 2010

[StreetTrader_13]

hi TP members..

sorry, di ko mahanap ang password ko sa file sharing sa internet...nilagay ko muna sa temporary web page ko.. actually, testing lang, nag aaral pa kasi ako ng html.. kung may problema, pm me lang..


here's the link for downloading intraday time & sales in yehey using excel macro... next time ko ipost yong iba pa...

http://www.geocities.com/wmytrade_13/Stocks/TS-Yehey.html

please follow the instruction at my web site...

note: If you don't have yehey cookies on your temporary internet file you need to follow instruction 1 to 3, if you already have yehey cookies, skip instruction 1 to 3..

sana makatulong...
regards,
_________________
kikoy's LATEST TRADINGCALC with autoUpdate - FASTER Download
______________
latest update as of Sept 26, 2010

[Lorbie]

madaming kinakabayo sila angping, wealth at tower ngayon..
geo, ni, mic, DIZ

[serval]

For some reason, I can't get the Downloader of Metastock to update the Metastock data (that comes from COL) using the CSV file converted by Yeahbah's Pseget.

However, here's a procedure to convert the COL metastock data into text file and then back to metastock format. Afterwards, we can use the Metastock Downloader to update the new metastock data using the CSV file converted by Pseget. This procedure also centralizes all symbols in one directory, unlike in the COL data where the symbols where separated by categories on each directory. If you want that original division of stock symbols by category, you have to experiment on how to do it. I don't know how to recreate it as of now.

I don't know much about Metastock except this conversion procedure. Just try to read/watch the tutorial and help files in how to use Metastock.

HOW TO USE THE METASTOCK DOWNLOADER

1. Use a text editor (notepad), put the following and save it as a batch file, for example cc.bat. Put it into c:\temp directory.

ms2asc -m MASTER -f financia.txt -i .\financia --ignoreTicker=yes --ignorePer=yes --ignoreTime=yes --ignoreOpenInt=no
ms2asc -m MASTER -f holding.txt -i .\holding --ignoreTicker=yes --ignorePer=yes --ignoreTime=yes --ignoreOpenInt=no
ms2asc -m MASTER -f index.txt -i .\index --ignoreTicker=yes --ignorePer=yes --ignoreTime=yes --ignoreOpenInt=no
ms2asc -m MASTER -f industri.txt -i .\industri --ignoreTicker=yes --ignorePer=yes --ignoreTime=yes --ignoreOpenInt=no
ms2asc -m MASTER -f prop.txt -i .\prop --ignoreTicker=yes --ignorePer=yes --ignoreTime=yes --ignoreOpenInt=no
ms2asc -m MASTER -f services.txt -i .\services --ignoreTicker=yes --ignorePer=yes --ignoreTime=yes --ignoreOpenInt=no
ms2asc -m MASTER -f mining.txt -i .\mining --ignoreTicker=yes --ignorePer=yes --ignoreTime=yes --ignoreOpenInt=no

2. Download the LZH files from COL into a temporary directory, for example c:\temp.

3. Extract the LZH files using winzip or izarc into the c:\temp directory.
You get the following directories with metastock data:
c:\temp\FINANCIA
c:\temp\HOLDING
c:\temp\INDEX
c:\temp\INDUSTRI
c:\temp\MINING
c:\temp\PROP
c:\temp\SERVICES

4. Download the files from http://www.geocities.com/mimansoor/MsCmdLineUtils.html and extract them in c:\temp.

5. Copy the following files below into c:\temp.

c:\temp\Metastock Data Conversion Utility\MsCmdLineUtilsV1.8.4\MsCmdLineUtilsV1.8.4\asc2ms\bin\win32\asc2ms.exe
c:\temp\Metastock Data Conversion Utility\MsCmdLineUtilsV1.8.4\MsCmdLineUtilsV1.8.4\asc2ms\bin\win32\pthread.dll
c:\temp\Metastock Data Conversion Utility\MsCmdLineUtilsV1.8.4\MsCmdLineUtilsV1.8.4\ms2asc\bin\win32\ms2asc.exe

You get the following:

c:\temp\pthread.dll
c:\temp\asc2ms.exe
c:\temp\ms2asc.exe

6. Using a command prompt window or Explorer, run the batch file cc.bat.
You get the following files:
c:\temp\financia.txt
c:\temp\holding.txt
c:\temp\index.txt
c:\temp\industri.txt
c:\temp\mining.txt
c:\temp\prop.txt
c:\temp\services.txt

7. Using a text editor (notepad), open each file and delete the first row. The first row is the following:
<NAME>,<DATE>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>,<OPENINT>

After deleting that entry, save each file.

8. Combine all the files by making a batch files (dd.bat) conataining the following:
copy financia.txt+holding.txt+index.txt+industri.txt+mining.txt+prop.txt+services.txt psedata.txt

9. Run the dd.bat batch file like before.

10. You now have one big file psedata.txt of around 20MB.

11. Since this text file is very big, if you want, you can open this file using a special text editor like Notetab.
http://www.fookes.com/ftp/free/NoteTab_Setup.exe (NoteTab Light 4.95 - Freeware)

12. Under Metastock, click Tools, click The Downloader, or just press Ctrl-D.

13. You'll be presented with a window. At first, for the Source and Destination, put the File Types to ASCII Text. Click Options. Set the following:

Source tab,
Date Range,
First Date: //
Last Date: //
Use Today's date: no check
max errors: 10000
max warnings:10000
Traverse source folders: no check
Periodicity:Daily

Destination Tab,
"If file exists," select "Append data to end of file", put check on "Replace matching dates."

Include Open & Open Interest: check
Create New files: check
Traverse destination folders: no check

Fields to output:
the following should have checks,
Symbol, Date, Open, high, low, close, volume, open interest

14. Click Ok and you go back to the main window.
-For source, select ASCII text
-for Filename, click the Browse button and select the psedata.txt we created in the c:\temp directory.

-For destination, select Metastock as File type.
-Click the Browse button and select the directory where you want to put in your metastock data, for example, c:\metastockdata.

15. Click Ok. A Convert Status will appear. Wait for the conversion to finish. After that, you can use Metastock to open any stock data in the c:\metastockdata directory or in any other directory where you selected to put your data.

16. To update the metastock data using PSEget, convert the pse daily quotation report pdf files into CSV format. After you get the CSV file, for example 2008feb11_stockquote.csv, select this file using the Downloader of Metastock as before. You can select multiple files. Just remember to put in *.CSV in the "File name" field, or set the "Files of type" to All Files(*.*) when selecting files so that the Downloader can see it. The downloader default to seeing TXT files.

17. Take note that the pse data from COL, has the following name for the indices:
ALL SSHARES INDE
FINANCIAL
HOLDINGS
INDUSTRIAL
MINING&O
PROPERTY
COMPOSITE
SERVICES
PSEADVDEC

The CSV file converted by PSEget has the following names for the indices:
^ALLSHARES
^FINANCIAL
^HOLDING
^INDUSTRIAL
^MINING-OIL
^PROPERTY
^PSEi
^SERVICE

You have to merge those symbols so that the names used by pseget will remain. In that way you can see more chart data for the indices. Unfortunately, I still don't know how to merge symbol names in Metastock, so you just have to read the help files of Metastock.

[dreadstar]

Yo streetrader_13 ... finally got intraday data into my stock charting software. SWEEET

Tnx bro!

[dreadstar]

Im curious with those people who use intraday charts. Do you view them as 'line chart of closing prices' or cnadlestick or OHLC?