Converting Metastock Data to Excel 2003

[ajcode]

Hi,

I've been working on a DLL (using the free GNU C++ (gcc) compiler and Codeblocks IDE) to read Metastock Data downloaded from a trial version of Stock Quote Data from DataHQ and are happy to upload on here for people to have a play with. Firstly, is this a forum where you can upload zip files? as .zip doesn't seem like a valid file extension and this is my first post?

I did upload an earlier version on the hometrader forum a couple of years ago, but can't be bothered paying another $600 to become an active member and have been told recently that my post about it since then was fairly inactive. I thought I'd give it another go on a free forum just in case people find it interesting as I've noticed some posts on this issue.

Anyway, my zip file is called "MSDR.zip" and contains the following files:

MSDRDLL.dll
MSDRDLLTesta.xls

The MSDRDLL.dll file contains all the executable code to access a Metastock Database and the excel 2003 file MSDRDLLTesta.xls contains the VBA code (which calls dll code) to display the data in excel.

In order to get things to work, the following needs to be done:

1) Metastock Data downloaded from DataHQ. Should be in a directory such as D:\TradingData\Stocks\ASX\Equities\.
2) Both files (from zip file) need to be in the same directory.
3) In Excel, Open file MSDRDLLTesta.xls and make sure Macros is enabled.
4a) In Excel, open macro window (Alt-F11) and search for the word "Initialize" and keep hitting find next until you get to the Initialize function.
4b) In Initialize() code, change the dbpath to where you have your metastock data installed (e.g. D:\Trading Data\Stocks\ASX\Equities\) and make sure there is a backslash at the end.

Sounds like a lot of work, which is probably why I never got much feedback on the hometrader forum, but could be useful if people are able to get it to work. I have only used it on a trial version from DataHQ which was the End Of Day ASX package that would of normally cost $15 back in 2012. It requires that Metastock data be in the format "Date, Open, High, Low, Close, Volume and Open Interest". If not it won't work. For example, it may not work for intraday data downloads as that might include a time.

Anyway, is anyone interested in this kind of stuff? and if so, am I able/allowed to upload the zip file?

Cheers,

Andrew.

 

[ajcode]

Reading my last post, I can see that if I was someone else reading it, I wouldn't know what the hell is being explained here and a picture says a thousand words so I have added a picture.

Here is an example of what should happen if you click on the SymbolQuote button. It picks up every single stock in the metastock database and displays the quotes for the current day or more precisely put, the most current day downloaded. It says 2012 in this example because I'm using a DataHQ trial metastock database from 2012. I hope this makes more sense.

Andrew

 

[ajcode]

Here is an example of what should happen if you click on the SingleSymbolQuote button. This button will activate a vba macro that tells my Metastock Data Reader dll to go get quotes for ANZ (from metastock database) and when it has done that, it will display them from current to oldest date fetched. ANZ is used in this case because with trial version from DataHQ, I think you get a lot more data for symbols that start with the letter A. Depending on your excel VBA skills, you could maybe have a drop down list to select from so that you could get the quotes for the symbol(/s) of your choice. You could also add moving averages and what not

 

[ajcode]

Here is an example where I have written a vba macro (which calls my MSDRDLL.dll code) to retrieve quotes for 20 highly liquid stocks from a Metastock Database and perform a correlation on all pairs starting from "current day" to 50 days back as specified in cell c6 in the diagram. A Correlation Matrix is then displayed in Sheet 1.



The "current day" in this example is in 2012 as I'm using the trial Metastock Database from DataHQ (explained in previous posts).

MSDRCorrelation.xls (attached below) contains the macro code that results in the above diagram. This file is to be put in the same directory as the MSDRDLL.dll file and requires the dbPath variable to point to your Metastock database as explained in previous posts.

View attachment MSDRCorrelation.xls

Cheers,

Andrew

 

[ajcode]

Re: (64 bit) Converting Metastock Data to Excel 2013

Re-visiting my first ever thread on ASF's

Thought I'd add the 64 bit DLL for 64 bit versions of Office such as 2013. I only needed to make changes to the function declarations in the .xls files. So now you can run my excel macros on both 32 bit and 64 bit excel installations, without having to modify the .xls files. Just need to use 32 bit DLL (in first post) for 32 bit installations and 64 bit DLL for 64 bit installations of Office.

Attached is file "MSDR64_20150818.zip". This zip files contains the following files:

1) MSDRDLL.dll (64 bit)
2) MSDRDLLTestb.xls (Modified file in first post)
3) MSDRCorrelation.xls (Modified file in previous post)

Just need to follow instructions in first post and change the dbPath to where your Metastock data is located in VBA editor.

Let me know if this works or not.

Cheers,

Andrew.

 

[ajcode]

Re: Converting Metastock Data to Excel

Just realized that when I run the file MSDRDLLTestb.xls (from previous post) on a 32 bit excel installation with the 32 bit dll (from first post) and click on the Symbol button, I get the following error message:

Run-time error '453':

Can't find DLL entry point MSDR_GetShortName_DLL in MSDRDLL.dll

This happened because somewhere between my first post and now, I added functionality to retrieve the short and long names of the security from the Metastock database. This functionality is in the 64 bit dll (previous post), so to be consistent, I have attached the updated 32 bit dll in zip file MSDR32_20150818.zip. I wondered why I renamed the excel file MSDRDLLTesta.xls to MSDRDLLTestb.xls.

TODO: Add scripts to show how to read from Metastock Database using DLL with Perl/Python etc., and output to .csv file.

Cheers,

Andrew.

 

[ajcode]

Using Perl/Python to Read Metastock Data

Thought I'd show how to read Metastock data using the below attached Perl/Python scripts. These scripts do basically what the macros in excel spreadsheet MSDRDLLTestb.xls do, but output the results to file "Output.csv".

The scripts are to be placed in the same directory as dll file "MSDRDLL.dll" and you need to change the directory where your Metastock Database files are stored like so:



Either can be executed from the command prompt and a menu is shown on startup as seen below:



Menu options 3 and above read from your Metastock Database and write filtered data to file "Output.csv".

Hopefully this should work on both 32 bit and 64 bit versions of both Perl and Python(2.7 and 3.4+). And remember to use 32 bit dll with 32 installations and 64 bit dll with 64 bit installations, otherwise error.

Probably the most "interesting" menu option is the TurnOver test. This test finds all stocks from specified Metastock database that have a TurnOver (Close x Volume) of $150000000 between 2 dates.

________________________________________________________________________________
To modify conditions in Perl script, change the following code:

# Set minimum turnover condition (change to suit your needs)
my $minto = 150000000;

# Set dates to search between condition (change to suit your needs)
my $syear = 2012; my $smonth=7; my $sday = 2; # Start Date
my $eyear = 2012; my $emonth=7; my $eday = 9; # End Date
________________________________________________________________________________
To modify conditions in Python script, change the following code:

# Set minimum turnover condition (change to suit your needs)
minto = 150000000

# Set dates to search between condition (change to suit your needs)
syear = 2012; smonth=7; sday = 2 # Start Date
eyear = 2012; emonth=7; eday = 9 # End Date
________________________________________________________________________________

"syear" means start year etc., and "eyear" means end year etc.


If you are moderate to good at programming scripts, you could add your own code to filter out stocks by say a Moving Average. Then you would have what is known as an End of Day (EOD) market scanner .

Cheers,

Andrew.

Please remove .txt extension from attached files below.

 

[mds2]

Hi AC,

I`ve just started learning Python - your post has been very helpful.

Thanks for your efforts!

Michael

 

[billrobpv]

Andrew
I have used Metalib50 (trading-tools.com) for several years, but it does not work with Excel2016 x64 systems (and Windows7). Been looking for a replacement for about 2 months, and your code is as close as anything I have found (and it is spectacular). I know Visual Basic, but not C++, Perl, or Python.
The files unzip OK. I put Metastock data (for about 10 securities) and the msdrdll into the folder “C:\MetaData\tradedata\”. When I run any of the subroutines I get the error message “File not found: MSDRDLL.dll”. I know I put the dll in the same folder as the data, and I have tried several variations on the code (initialize) dbPath = “C:\MetaData\tradedata\”. I can send you my code if it would help.
Do I need to register the dll? Any suggestions would be appreciated. Bill Roberts

 

[ajcode]

Andrew
I have used Metalib50 (trading-tools.com) for several years, but it does not work with Excel2016 x64 systems (and Windows7). Been looking for a replacement for about 2 months, and your code is as close as anything I have found (and it is spectacular). I know Visual Basic, but not C++, Perl, or Python.
The files unzip OK. I put Metastock data (for about 10 securities) and the msdrdll into the folder “C:\MetaData\tradedata\”. When I run any of the subroutines I get the error message “File not found: MSDRDLL.dll”. I know I put the dll in the same folder as the data, and I have tried several variations on the code (initialize) dbPath = “C:\MetaData\tradedata\”. I can send you my code if it would help.
Do I need to register the dll? Any suggestions would be appreciated. Bill Roberts

Hi Bill,

As you are using 64 bit excel, I would assume that you are using the 64 bit dll in MSDR64_20150818.zip right? As 32 bit dlls do not work with 64 bit calling applications such as 64 bit excel 2016 and vice-versa.

Secondly, do not put the dll in the same directory/folder as the data. What you need to do is put the dll in the same directory/folder as the excel files (.xls). So say you download and unzip MSDR64_20150818.zip to C:\tmp. This directory/folder should contain the following files:

MSDRCorrelation.xls
MSDRDLL.dll
MSDRDLLTestb.xls

Then what you do is change dbpath in the following subroutine:

Sub Initialize(dbPath As String)
___dbPath = "C:\MetaData\tradedata\"
.
.
.
End Sub

and then it should work .

You will notice at the start of my macro subroutines (where button is assigned to), I have the following code:

Dim Path As String: Path = ThisWorkbook.Path
Dim Drive As String: Drive = Left(Path, 1)
ChDrive (Drive)
ChDir (Path)

This tells excel to set the default/current working directory/folder to where the .xls file is stored. I do this so that I don't have to hard code my vba dll declarations (at start of file) to where the dll file is stored. As soon as the subroutine is run, excel will search the set current working directory (where .xls file is stored) for the dll file. If you do not put the dll file in same location as .xls file, you will probably get a file not found error.

Hope this helps,

Andrew.

 

[billrobpv]

Andrew;
I put MSDRDLLTestb.xls and MSDRDLL.dll in the folder C:\atmp, and confirmed the database in
C:\MetaData\tradedata. I also commented out all the 32 bit function declarations to be sure I was running 64 bit. Then (since they were no longer needed) I commented out #If VBA7 then, #Else, and
#EndIf.
The command line reads “dbPath = C:\MetaData\tradedata\”
Now I get a DLL Error Message – Funct = LoadIndex, with the text “Wrong Record Structure”. I am confident that I unzipped the 64bit dll. Two questions:
1. I am not familiar with the “#” prefix in VB. Although I get the same error whether or not the 32bit declarations are active, what does it do??
2. Is it possible there is something different in the Metastock Downloader? Could I be downloading data that is not formatted for 64bit? Thanks for any help. Bill Roberts

 

[ajcode]

Andrew;
I put MSDRDLLTestb.xls and MSDRDLL.dll in the folder C:\atmp, and confirmed the database in
C:\MetaData\tradedata. I also commented out all the 32 bit function declarations to be sure I was running 64 bit. Then (since they were no longer needed) I commented out #If VBA7 then, #Else, and
#EndIf.
The command line reads “dbPath = C:\MetaData\tradedata\”
Now I get a DLL Error Message – Funct = LoadIndex, with the text “Wrong Record Structure”. I am confident that I unzipped the 64bit dll. Two questions:
1. I am not familiar with the “#” prefix in VB. Although I get the same error whether or not the 32bit declarations are active, what does it do??
2. Is it possible there is something different in the Metastock Downloader? Could I be downloading data that is not formatted for 64bit? Thanks for any help. Bill Roberts

Bill, it's good that you got that error message, because that tells me that you set things up correctly. The bad is my dll is very strict on the database format. It only does EOD (End of Day) and must be in the following format:

Date, Open, High, Low, Close, Volume, Open Interest.

So if it's not EOD with the 7 column order specified above, then you will get the error message "Wrong Record Structure".

I designed it this way because what most people do is download metastock data from a paid service such as Premium Data and this is the format that paid services often use. You're the first person that has done things differently and it looks like you're converting csv files to metastock files using the Metastock Downloader. If you do this then my dll will complain if you do not use the format specified above. It's very strict

As I like my vba code to be compatible with as many versions of excel as possible, from I think excel 2010, VBA 7 was introduced to allow for 64 bit types. This is why you see #If VBA7. When declaring vba dll prototype functions in later versions of excel, an extra bit of code (namely PtrSafe) is required to compile properly.

So problem now is matching the format specified above with your Metastock Downloader. Anything other than EOD simply will not work with my dll

Cheers,

Andrew

 

[billrobpv]

Andrew;
Well, I guess that's progress . Metastock Downloader is a Metastock product (paid service). I put in a list of symbols, and get a DAT file for each symbol with two indexes "XMaster" and "Master". I would argue that (by definition) that must be the correct Metastock format. It is EOD data, and the sequence is the same (OHLCV). The only difference is that I do not download Open Interest. Might be something to chew on.

The frustration is that Metastock considers their code proprietary. They have a "convert" program in the Downloader that converts to an Excel file, exactly what I want "imbedded" in my VB code, but they will not help with the code so I can do that.

I would be glad to send you a downloaded file, if you wish to look at it. I'm not sure how to attach a file to these posts, but if you would email to bc.roberts@hotmail.com, I'll reply and attach a file. Thank you for the help. Bill Roberts

 

[billrobpv]

News at 11:00 !!
When I add Open Interest to the Downloader options, I get the data. So the dll is restrictive, but I am meeting MetaStock format requirements.
Moving on, I want to put in a symbol and specify the number of days to load (default is 400). I wanted to pass the above info asap, so I haven't "worked the problem".
I tried (VERY briefly) to punch "Single Symbol Quote" and I get "Symbol not found". I can probably fix this myself, but maybe you can give me a quick answer, especially regarding the number on days (oldest first). Thanks. Bill Roberts

 

[ajcode]

News at 11:00 !!
When I add Open Interest to the Downloader options, I get the data. So the dll is restrictive, but I am meeting MetaStock format requirements.
Moving on, I want to put in a symbol and specify the number of days to load (default is 400). I wanted to pass the above info asap, so I haven't "worked the problem".
I tried (VERY briefly) to punch "Single Symbol Quote" and I get "Symbol not found". I can probably fix this myself, but maybe you can give me a quick answer, especially regarding the number on days (oldest first). Thanks. Bill Roberts

Well done for getting this far. I didn't think you'd be able to get it to work but you did . "Symbol not found" is occurring because the subroutine A09_SingleSymbolQuoteTest or A10_SingleSymbolQuoteTestRO (RO = reverse order) is expecting the dll to find Aussie symbol "ANZ" which is hard coded into these vba subroutines. This is easily fixed by adding the security you want to look at. In the vba code, just change Symbol = "BillsSymbol" and it should work. If you only want to look at the last 400 bars, change QuoteArraySize to 400. I think the maximum is 1500, just in case this ever goes viral LOL .

When you set QuoteArraySize to 400, all the Open,High,Low,Close etc., arrays are dynamically allocated to 400 array elements and then I call MSDR_SetGlobalLookback_DLL(QuoteArraySize) to set the maximum array elements (quotes) to return. If a security doesn't have 400 bars due to not being around for that long then MSDR_GetQuotes_DLL will return the number of quotes fetched.

Calling functions ending with RO fetches quotes in reverse order oldest to newest as opposed to newest to oldest. I probably should have designed it the other way around as that would make more sense, but these things happen and it's not a problem. This is important if you want to calculate say moving averages, because you need things the right way around to make sense.

 

[ajcode]

Andrew;
Well, I guess that's progress . Metastock Downloader is a Metastock product (paid service). I put in a list of symbols, and get a DAT file for each symbol with two indexes "XMaster" and "Master". I would argue that (by definition) that must be the correct Metastock format. It is EOD data, and the sequence is the same (OHLCV). The only difference is that I do not download Open Interest. Might be something to chew on.

The frustration is that Metastock considers their code proprietary. They have a "convert" program in the Downloader that converts to an Excel file, exactly what I want "imbedded" in my VB code, but they will not help with the code so I can do that.

I would be glad to send you a downloaded file, if you wish to look at it. I'm not sure how to attach a file to these posts, but if you would email to bc.roberts@hotmail.com, I'll reply and attach a file. Thank you for the help. Bill Roberts

Didn't see this post earlier on. Kept clicking on your update in the main forum and there was nothing to look at . Then I see your later post all of a sudden and replied to that.

You are quite right about Metastock format. It's their product so (by definition) their format is THE FORMAT . What you need to understand here is that they are playing international cricket and I'm playing backyard cricket (pub rules) . And in backyard cricket, the boundaries are a lot smaller and restrictive .

Cheers,

Andrew.

 

[ajcode]

Using Perl/Python/Excel to Read Metastock Data

Hi AC,

I've just started learning Python - your post has been very helpful.

Thanks for your efforts!

Michael

No worries Michael

Now I have some more useful stuff I want to add to this thread:

Attached is file MSDRSPFDLL_20160424.zip. This zip file contains the following files:

MSDRSPFDLLTest.pl (perl script)
MSDRSPFDLLTest.py (python script)
MSDRSPFDLLTest.xls (excel vba script)

These files are a continuation of reading financial data from a Metastock Database, but I have now added Technical Analysis functions from my other thread. Namely:

Technical Analysis DLL for use with Windows Software

https://www.aussiestockforums.com/forums/showthread.php?t=30859

So If you grab my SPF.dll from the above thread link along with the MSDRDLL.dll posted earlier in this thread and put these in the same directory as the unzipped files above (making sure you're matching the correct bit architecture), it should look like this:



Important: As talked about in earlier posts, you need to edit the scripts to where your Metastock Database is stored otherwise they won't work.

My scripts contain 2 very basic technical analysis scans:

Scan 1 scans the Metastock Database looking for all securities whose slow stochastic has fallen below 20 or risen above 80 in the last 2 days including the current day and also having 30 day average turnover > $15 million. This is the more popular scan because we are looking at the most recent data downloaded from your metastock provider. Other Indicator Values such as Welles Wilder ATR are also displayed to show more of the functionality from my Technical Analysis DLL.

Scan 2 scans the Metastock Database looking for all securities whose 30 day average turnover > $x between 2 dates. The user is asked to input the minimum turnover ($x) constraint and also what dates to search between.

So with a bit of coding effort, you can now perform scheduled technical analysis scans on metastock data after downloading updates from say premium data every night

Cheers,

Andrew.

 

[coldsteel007]

AJ code, this is a wonder code that you have written. I used the python code and it is amazing. However, i notice that for any given stock, the DLL can go back an fetch only last 1500 dates. What if my dataset has data for last 3000 dates. Infact I do have a dataset which has ~2500 dates and I need to access information that far back. How do I access this? Do I need to edit the DLL. If so then how?