Using MS Access/Excel 32bit & 64 bit while using MC 64 bit  [SOLVED]

Questions about MultiCharts and user contributed studies.
bowlesj3
Posts: 2083
Joined: 21 Jul 2007
Has thanked: 196 times
Been thanked: 414 times

Using MS Access/Excel 32bit & 64 bit while using MC 64 bit

Postby bowlesj3 » 14 Sep 2017

I just found out that MS-Access 64 bit can be used with the MC 64 bit dll for the purpose of sending/receiving data to/from MC 64 bit. For any who may be interested I included below the MS-Access code that passed the the test which was done by someone who has the 64 bit MS-Access (I will be buying it soon) . The "PtrSafe" command is the command that allows the DLL function definitions to be accepted by the 64 bit MS-Access. If you want to run these with MS-Access 32 bit you need to remove the PtrSafe command. In the example below (for the purpose of testing) the dll was renamed "GlobalVariable_64bit.dll" and located in the C:\ directory. You will probably want to use the original dll which is located in the directory that MC uses and point these declare functions at it. I had to create my own wrapper functions in MC to cover off every possible data type that is available with the GVs (boolean, double, integer, float and string). I decided to do this since I figure I am going to run out of named global variables if I am only using (int, float and string). In other words I can scan more stocks with all 5 types available. Creating some of the ones that were missing was a bit of a challenge so if anyone is having trouble creating some of the ones that are missing let me know and I will add them to this thread.

Code: Select all

Option Compare Database
Option Explicit

Private Declare PtrSafe Function GV_GetNamedBool Lib "C:\GlobalVariable_64bit.dll" _
                                       (ByVal intOther As Boolean, ByVal strElementLoc As String) As Boolean
Private Declare PtrSafe Function GV_SetNamedBool Lib "C:\GlobalVariable_64bit.dll" _
                                       (ByVal strElementLoc As String, ByVal intSetValue As Boolean) As Long

Private Declare PtrSafe Function GV_GetNamedDouble Lib "C:\GlobalVariable_64bit.dll" _
                                       (ByVal strElementLoc As String, ByVal intOther As Double) As Double
Private Declare PtrSafe Function GV_SetNamedDouble Lib "C:\GlobalVariable_64bit.dll" _
                                       (ByVal strElementLoc As String, ByVal intSetValue As Double) As Long

Private Declare PtrSafe Function GV_GetNamedInt Lib "C:\GlobalVariable_64bit.dll" _
                                        (ByVal strElementLoc As String, ByVal intOther As Single) As Long
Private Declare PtrSafe Function GV_SetNamedInt Lib "C:\GlobalVariable_64bit.dll" _
                                       (ByVal strElementLoc As String, ByVal intSetValue As Long) As Long

Private Declare PtrSafe Function GV_GetNamedFloat Lib "C:\GlobalVariable_64bit.dll" _
                                       (ByVal strElementLoc As String, ByVal intOther As Single) As Single
Private Declare PtrSafe Function GV_SetNamedFloat Lib "C:\GlobalVariable_64bit.dll" _
                                       (ByVal strElementLoc As String, ByVal intSetValue As Single) As Long

Private Declare PtrSafe Function GV_GetNamedString Lib "C:\GlobalVariable_64bit.dll" _
                                       (ByVal strElementLoc As String, ByVal strOther As String) As String
Private Declare PtrSafe Function GV_SetNamedString Lib "C:\GlobalVariable_64bit.dll" _
                                       (ByVal strElementLoc As String, ByVal intSetValue As String) As Long


'=========================================================================================
'START: Booleans
Public Function A_GV_GetNamedBool64(strName As String, blnErrorCode As Boolean) As Boolean
    A_GV_GetNamedBool64 = GV_GetNamedBool(blnErrorCode, strName)
End Function

Public Function A_GV_SetNamedBool64(strName As String, dblGVvalue As Boolean) As Long
    A_GV_SetNamedBool64 = GV_SetNamedBool(strName, dblGVvalue)
End Function

'=========================================================================================
'START: Doubles
Public Function A_GV_GetNamedDouble64(strName As String, lngErrorCode As Long) As Double
    A_GV_GetNamedDouble64 = GV_GetNamedDouble(strName, lngErrorCode)
End Function

Public Function A_GV_SetNamedDouble64(strName As String, dblGVvalue As Double) As Long
    A_GV_SetNamedDouble64 = GV_SetNamedDouble(strName, dblGVvalue)
End Function

'=====================================================================================
'START: Integers
Public Function A_GV_GetNamedInt64(strName As String, lngErrorCode As Long) As Long
    A_GV_GetNamedInt64 = GV_GetNamedInt(strName, lngErrorCode)
End Function

Public Function A_GV_SetNamedInt64(strName As String, lngGVvalue As Long) As Long
    A_GV_SetNamedInt64 = GV_SetNamedInt(strName, lngGVvalue)
End Function

'=========================================================================================
'START: Floats
Public Function A_GV_GetNamedFloat64(strName As String, lngErrorCode As Long) As Single
    A_GV_GetNamedFloat64 = GV_GetNamedFloat(strName, lngErrorCode)
    End Function

Public Function A_GV_SetNamedFloat64(strName As String, sngGVvalue As Single) As Long
    A_GV_SetNamedFloat64 = GV_SetNamedFloat(strName, sngGVvalue)
End Function

 
'===========================================================================================
'START: Strings
Public Function A_GV_GetNamedString64(strName As String, strErrorCode As String) As String
    A_GV_GetNamedString64 = GV_GetNamedString(strName, strErrorCode)
End Function

Public Function A_GV_SetNamedString64(strName As String, strGVvalue As Variant) As Long
    A_GV_SetNamedString64 = GV_SetNamedString(strName, strGVvalue)
End Function
Last edited by bowlesj3 on 19 Sep 2017, edited 1 time in total.
These users thanked the author bowlesj3 for the post (total 2):
TJarjfca

arjfca
Posts: 1276
Joined: 23 Nov 2010
Has thanked: 722 times
Been thanked: 222 times

Re: Tip! Using MS-Access with MC 32 bit and MC 64bit

Postby arjfca » 14 Sep 2017

Hello John

Not sure to understand.
Can I use MC 64 global variable and be able to exchange info between Excel / Access 32 bit?

Martin

bowlesj3
Posts: 2083
Joined: 21 Jul 2007
Has thanked: 196 times
Been thanked: 414 times

Re: Tip! Using MS-Access with MC 32 bit and MC 64bit

Postby bowlesj3 » 14 Sep 2017

Hi Martin,

if you are using MC 64 bit (which uses the 64 bit dll for the Global Variables) you need to be using MS-Excel 64 bit or MS-Access 64 bit to exchange the Global Variable Information. This is not to be confused with the 64 bit Windows operating system which can run MS-Excel and MS-Access 32/64 bit.

The web page below explains how you can know for sure which MS-Office you have (32 bit or 64 bit).
http://portal.presentationpro.com/kb/a24/what-version-of-office-am-i-using-32-bit-or-64-bit.aspx#version
You probably know that MS-Office 2016 does not come with MS-Access. MS-Access 2016 must be downloaded (not sure where to get older versions).
I have not done it yet so I am not sure how to guarantee I get the 64 bit version of MS-Access (maybe it comes with both).

Hope that helps,
John
Last edited by bowlesj3 on 14 Sep 2017, edited 1 time in total.

arjfca
Posts: 1276
Joined: 23 Nov 2010
Has thanked: 722 times
Been thanked: 222 times

Re: Tip! Using MS-Access with MC 32 bit and MC 64bit

Postby arjfca » 14 Sep 2017

OK
I will continu with the Text file to exchange data between MC 64 and Excel 32

Martin

bowlesj3
Posts: 2083
Joined: 21 Jul 2007
Has thanked: 196 times
Been thanked: 414 times

Re: Tip! Using MS-Access with MC 32 bit and MC 64bit

Postby bowlesj3 » 14 Sep 2017

Sticking with 32 bit ms-office is probably your best bet because apparently 64 bit ms-office is not recommended nor supported as this link points out.
http://kb.mit.edu/confluence/pages/viewpage.action?pageId=155269580

However, if your trading system is making good money as a discretionary system and your system is too complex to program for auto-trading such that you use MC in combination with MS-Access to simply point out possible trades and if you absolutely need MC 64 bit then it makes sense to get MS-Access 64 bit and run them on a separate machine all by themselves. That is the decision I will be making. I am going to try and program as best as I can my trading system and it if does not work well enough I will get a separate machine and put them on it. The combo will reduce my workload but I still have to make the decision by doing what is very difficult to program (keep in mind the big picture).

bowlesj3
Posts: 2083
Joined: 21 Jul 2007
Has thanked: 196 times
Been thanked: 414 times

Re: Tip! Using MS-Access with MC 32 bit and MC 64bit

Postby bowlesj3 » 14 Sep 2017

I was just reminded of a software called hyper-v. It is used to segment your real machine's ram memory into virtual machines. According to my understanding if you have 16 gig of memory on your machine you can setup one virtual machine for running win 64 bit which runs 32 bit office and another virtual machine for running win 64 bit which runs 64 bit MC and 64 bit MS-Access or 64 bit MS-excel. Apparently this is extremely stable (totally reliable). It is apparently extremely fast because it is hardware based. So in other words if you have a fast i5 quad core machine you can run your MC 64 bit and Excel/Access 64 bit basically as if it was literally a different machine. On your monitors you switch between the virtual machines very quickly. I have never done this but my source obviously has and I would have to assume it is only a slight bit slower. Apparently the Hyper-v software allocates memory between the two virtual machines as needed so if you have a lot of stocks in MC 64 it will provide the memory needed (it has complete control and the two windows that are running have no idea what is going on as far as they are concerned). I can't confirm this with my own experience but I plan on trying it out eventually before I buy another box. I have to assume you can reboot the one virtual machine and leave the other running all the time if you wish (turning off one of the operating systems under the virtual machine software does not turn off the box - only the hyper-v virtual machine software could do that). I did a bit of google research and it appears you have to make sure you get the correct one for your operating system (meaning the virtual machine software is designed to set up a virtual machine than can accept your operating system's install). I have to assume it is wise to take a full image backup or two before this is tried.

Meecc
Posts: 35
Joined: 23 Jun 2011
Has thanked: 19 times
Been thanked: 6 times

Re: Tip! Using MS-Access with MC 32 bit and MC 64bit

Postby Meecc » 15 Sep 2017

"bowlesj3"
I had to create my own wrapper functions in MC to cover off every possible data type that is available with the GVs (boolean, double, integer, float and string). I decided to do this since I figure I am going to run out of named global variables if I am only using (int, float and string). In other words I can scan more stocks with all 5 types available. Creating some of the ones that were missing was a bit of a challenge so if anyone is having trouble creating some of the ones that are missing let me know and I will add them to this thread.


I just wonder if it will help to increase the named GV limit of 3000 slots by rename the original GV dll and use as another set of the GV dll file.

I mean if we make a copy of the original GVs dll , rename this new dll file
and reference to this new dll but
need to make certain referencing the correct dll when caller call to either the old or new dll file.

Will it work ?
Last edited by Meecc on 19 Sep 2017, edited 1 time in total.
These users thanked the author Meecc for the post (total 2):
bowlesj3TJ

bowlesj3
Posts: 2083
Joined: 21 Jul 2007
Has thanked: 196 times
Been thanked: 414 times

Re: Tip! Using MS-Access with MC 32 bit and MC 64bit

Postby bowlesj3 » 15 Sep 2017

Interesting idea. I will give it a try. If it works it will be a great idea :-)

bowlesj3
Posts: 2083
Joined: 21 Jul 2007
Has thanked: 196 times
Been thanked: 414 times

Re: Tip! Using MS-Access with MC 32 bit and MC 64bit

Postby bowlesj3 » 16 Sep 2017

I did the test in MS-Access (setting up a second GlobalVariables2.dll file and a module with definitions pointing to that file instead). It works with all 5 types while the original file name can be used as well.

So your idea has reached great idea status :D
It was extremely easy to set up (10 minutes maybe).
I think you need to take an IQ test so see if you are a genius Meecc :wink:
I think others should click your thank you button too.
These users thanked the author bowlesj3 for the post (total 2):
TJMeecc

bowlesj3
Posts: 2083
Joined: 21 Jul 2007
Has thanked: 196 times
Been thanked: 414 times

Re: Tip! Using MS-Access with MC 32 bit and MC 64bit

Postby bowlesj3 » 19 Sep 2017

Another option which may be better and which is also free is virtualbox. It can run on Win-7 or Win 10 whereas Hyper-v needs Win 10 professional. It is my understanding you can be running 32 bit office on your main setup then use virtualbox to create another virtual machine on which you can run another Windows 64 bit operating system along with 64 bit MC and 64 bit MS-Excel or 64 bit MS-Access. If the main windows goes down the virtualbox will go down but at least you do not need to disturb your main windows setup. So your added expense is possibly another Windows install on the virtual machine and the 64 bit MS-excel or MS-Access. You do a complete image backup of the whole drive after you get it setup and backup your data daily. After I get my MC scripts finalized using 32 bit MC and a small set of stocks I will be taking this VirtualBox route to increase the number of stocks rather than the hyper-v route. It hopefully should allow me to run on 16 gig of ram on my current machine. If not and I have to go to a machine with more memory I will probably go the Win 10 professional route with with hyper-v so I can run both the 32 bit systems and the 64 bit systems at the same time.

sptrader
Posts: 674
Joined: 09 Apr 2010
Location: Colorado
Has thanked: 438 times
Been thanked: 251 times
Contact:

Re: Tip! Using MS-Access with MC 32 bit and MC 64bit

Postby sptrader » 20 Sep 2017

bowlesj3 wrote:Another option which may be better and which is also free is virtualbox. It can run on Win-7 or Win 10 whereas Hyper-v needs Win 10 professional. It is my understanding you can be running 32 bit office on your main setup then use virtualbox to create another virtual machine on which you can run another Windows 64 bit operating system along with 64 bit MC and 64 bit MS-Excel or 64 bit MS-Access. If the main windows goes down the virtualbox will go down but at least you do not need to disturb your main windows setup. So your added expense is possibly another Windows install on the virtual machine and the 64 bit MS-excel or MS-Access. You do a complete image backup of the whole drive after you get it setup and backup your data daily. After I get my MC scripts finalized using 32 bit MC and a small set of stocks I will be taking this VirtualBox route to increase the number of stocks rather than the hyper-v route. It hopefully should allow me to run on 16 gig of ram on my current machine. If not and I have to go to a machine with more memory I will probably go the Win 10 professional route with with hyper-v so I can run both the 32 bit systems and the 64 bit systems at the same time.


Oracle's Virtualbox is pretty cool, you can run any OS you desire.. I even have XP and 2 ver of Linux there (you can have multiple OS) and both work great... I needed XP for an old AI program that didn't work well with Win7 or Win10. Linux is just to test different versions and tinker with.
I don't think that Virtual box is as fast but it's a very flexible environment.
These users thanked the author sptrader for the post:
bowlesj3

bowlesj3
Posts: 2083
Joined: 21 Jul 2007
Has thanked: 196 times
Been thanked: 414 times

Re: Tip! Using MS-Access with MC 32 bit and MC 64bit

Postby bowlesj3 » 20 Sep 2017

I don't think that VirtualBox is as fast but it's a very flexible environment.


This is another reason I might choose to get a special machine using hyper-v after using virtualbox for a while on my current win7 machine. But at least with virtualbox on my current machine I can test the two MCs against each other and see if there is a speed difference. You could time the scripts recalculating after a compile for example.

bomberone1
Posts: 189
Joined: 02 Nov 2010
Has thanked: 14 times
Been thanked: 12 times

Re: Using MS Access/Excel 32bit & 64 bit while using MC 64 bit

Postby bomberone1 » 04 Oct 2017

Could someone give any example about mc and access?
Is it usefull and what about?

bowlesj3
Posts: 2083
Joined: 21 Jul 2007
Has thanked: 196 times
Been thanked: 414 times

Re: Using MS Access/Excel 32bit & 64 bit while using MC 64 bit

Postby bowlesj3 » 05 Oct 2017

I use MS-Access primarily because (as I see it after staring at the market 8/7 for about 7 years and 8/5 for an additional 5 years) the market moves in multi-levels of waves/swings as well as bounces (what some call resistance points). It also does trend line breaks but I do not use price lines any more (6 years of that frustration was enough for me so I found a better way). Getting MultiCharts scripts to read the swings properly (as I see it) is very difficult. Notice I said multi-levels. In other words you will see a big wave in the daily bars for example and it is growing in one particular direction and as it swings back it will typically come back in smaller waves and it is the reading of the smaller waves that typically allows you to figure out where to get a good price on it. Once you hopefully get your good price the bigger wave shifts to start moving forward again (again it can bounce at times rather than do a smooth shift in the smaller waves). Not only that the waves often form very specific patterns which are next to impossible to program yet the eye can see these very quickly (at least I can but that may be because I spent so much time analysing the market long before I started touching a computer program such as MC). At present I have only been able to get MC to read the waves correctly 80% to 90% of the time (a big jump up from prior attempts where I was only able to get it to read them correctly maybe 40% of the time). This is because (after programming for about 30 years) I have never seen anything more difficult than programming market price structure. I have recently programmed 5 approaches to finding swings/waves in the market and I have selected what I consider the best one. But 90% isn't very good and I don't trust the scripts to get it correct because they have proven they can't. What I have not been able to program is the fact that I see time as well as price distance (not to mention the complex patterns as well). So what I do is I use MC as an assistant. It points things out to me such as a profit increase in several levels of waves and MS-Access using a timer loop detects whatever MC finds and tells me about it using copy to clipboard with a Text To Speech reader. I then check it out manually to see if I agree with it. It it gets it wrong it is almost always because there was a time factor that it missed where the waves were smaller but they matched by time. So it will search 100 stocks constantly (something I can't do) and I check each one out individually (maybe 3 per hour). If it gets it correct I go for it. I can then use MS-Access to control MC to a certain extent. For example it opens a workspace for me automatically so I can be holding a position while the scanner continues to search for other trades. I also tell it through MS-Access which wave I want to trade and it can use this info to read the waves to hopefully tell me when to get out rather than having the Interactive Brokers stop loss take me out. MS-Access picks up the trailing stop percentages through MC's reading of the waves after I decide which wave I want to ride. I punch the two percentages into the MC chart trader. MC tells me when it thinks I should get out and once again I check to make sure it gets it correct. If I agree I use the chart trader to exit. It could be that MC gets it wrong and I get stopped out rather than get the better price at a wall bounce or a lower level child wave shift. Such is life. So in short MS-Access and MC combined point out trades to me and also help me manage a trade so I work less. I have accepted the fact that the scripts will never be 100% as good as my read of the market but at the same time they can read their limited ability to see a lot faster than I can and more accurately than I can. It is a trade off trying to get the best of both worlds.

Another example. I mentioned I use the scanner to scan 50+ stocks. However when I take a trade I open a special workspace for each. I could be holding 5 while scanning 100. So that means I need 100 workspaces available so I can be holding any 5 of those 100 stocks. If I make a change to a hold workspace such as adding a new script that means I have to do this change to 99 other workspaces. MS-Access makes this easy. I run a script that copies the change from the first workspace to the remaining 99. After doing the file copy it changes the symbol on each of the 6 charts within each of the remaining workspaces. I tried linking the charts but this does not work. So it has to change 6 charts. I can go to sleep, take a break, whatever while it does this.
These users thanked the author bowlesj3 for the post:
Meecc

bowlesj3
Posts: 2083
Joined: 21 Jul 2007
Has thanked: 196 times
Been thanked: 414 times

Re: Using MS Access/Excel 32bit & 64 bit while using MC 64 bit  [SOLVED]

Postby bowlesj3 » 17 Nov 2017

Just a follow-up. For a while I have been running MS-Access 64 bit reading and writing Global variables for the purpose of communicating with MC 64 bit and it is working well.


Return to “MultiCharts”