Success! Real Time Data into MS-Access! Any Better Way?

Questions about MultiCharts and user contributed studies.
bowlesj3
Posts: 2028
Joined: 21 Jul 2007
Has thanked: 174 times
Been thanked: 405 times

Success! Real Time Data into MS-Access! Any Better Way?

Postby bowlesj3 » 08 Aug 2007

I finally managed to get the real time Bollinger band data from MultiCharts into MS-Access.

Before I go on, please do not tell me that MultiCharts can do what I plan on doing in MS-Access.
I know easy language well enough and
MS-Access well enough to know it can not.

So I had to use an input statement in MS-Access
Because MultiCharts had the file locked.

The problem is the print statement I used at the
Bottom of the built in Bollinger Bands function
Writes out more lines than I need even though I
Included the “LastBarOnChart” statement. It also
Keeps increasing the size of the file.

The question is how to get it to write out only 1 line
Or at least only a few lines.

By the way I tested this by creating a form in
MS-Access (one thing that MultiCharts can not do)
And placing a button on the form to call the routine
Which gets the data and the MSGBOX command
Displays only the data I wanted to see. Specifically
The last line of that big file.

By the way the test was put in the 15 minute bar chart.
In the end I need to call a routine for all bar chart
sizes (WB, DB, 60, 30, 15,10,5 and 1).
Maybe even a 5 second bar chart. I won't give away
my trading system but this is a clue as to why
I need MS-Access. It will organize that data for me.

Unfortuately I do not know "C" or how to write DLLs or anything
about how to do DDE. This would be the best way but within the
confines of my current knowledge and unwillingness to pay to
have it programmed I am hoping to find a way to get
only the last line of this file out.

The good news is I can adjust any of the bands on any of the charts in only one place (the normal place) and MS-Access will have the latest real time figures ready for me to click a button and get the info the way I want it.


Here is the MultiCharts Code. It has a print statement.
=======================================================

inputs:
BollingerPrice( Close ),
TestPriceUBand( Close ),
TestPriceLBand( Close ),
Length( 20 ),
NumDevsUp( 2 ),
NumDevsDn( -2 ),
Displace( 0 ) ;

variables:
Avg( 0 ),
SDev( 0 ),
LowerBand( 0 ),
UpperBand( 0 ) ;

Avg = AverageFC( BollingerPrice, Length ) ;
SDev = StandardDev( BollingerPrice, Length, 1 ) ;
UpperBand = Avg + NumDevsUp * SDev ;
LowerBand = Avg + NumDevsDn * SDev ;

if Displace >= 0 or CurrentBar > AbsValue( Displace ) then
begin
Plot1[Displace]( UpperBand, "UpperBand" ) ;
Plot2[Displace]( LowerBand, "LowerBand" ) ;
Plot3[Displace]( Avg, "MidLine" ) ;

{ Alert criteria }
if Displace <= 0 then
begin
if TestPriceLBand crosses over LowerBand then
Alert( "Price crossing over lower price band" )
else if TestPriceUBand crosses under UpperBand then
Alert( "Price crossing under upper price band" ) ;
end ;
end ;

if LastBarOnChart then
begin
Print( File("C:\Access\MC_BB_15.txt"), Barnumber, "," , Close:8:4, ",", UpperBand:8:4, ",", Avg:8:4, "," , LowerBand:8:4);
end;



Here is the MS-Access Code
=======================================================

Option Compare Database
Option Explicit

'This example uses the Input # statement to read data from a file into variables. It reads every line of the file
'It leave the variables with the latest data which would be showing
'on the right hand side price scale of the screen.
'This gets output by the MSGBOX command so I can test the data
'to see if it is indeed the real time last tick data.

Public Function funcGetData()

Dim sngBarNumber As Single
Dim sngClose As Single
Dim sngUpperBand As Single
Dim sngAvg As Single
Dim sngLowerBand As Single

Open "C:\Access\MC_BB_15.txt" For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Loop until end of file.
Input #1, sngBarNumber, sngClose, sngUpperBand, sngAvg, sngLowerBand
Loop
Close #1 ' Close file.
MsgBox "Close=" & sngClose & " Upper=" & sngUpperBand & " Middle=" & sngAvg & " Lower=" & sngLowerBand
End Function

Guest

Postby Guest » 08 Aug 2007

This dll is designed to work from TS to Excell, but it should work with Access too.

www.traderssoft.com/sys/tsl/

PS: Isnt it better to stick all your BB-Band and Access questions into one thread.

User avatar
ABC
Posts: 524
Joined: 16 Dec 2006
Has thanked: 111 times
Been thanked: 295 times
Contact:

Re: Success! Real Time Data into MS-Access! Any Better Way?

Postby ABC » 08 Aug 2007

The problem is the print statement I used at the
Bottom of the built in Bollinger Bands function
Writes out more lines than I need even though I
Included the “LastBarOnChart” statement. It also
Keeps increasing the size of the file.

The question is how to get it to write out only 1 line
Or at least only a few lines.



bowlesj3,

the reason why you get multiple lines per bar is that the code is executed several times per bar i.e. on every tick or on every significant change.

If you want a new line at the close of each bar only, then use

Code: Select all

If BarStatus(X) = 2 then begin
//where X is the number of your datastream i.e. X=1 for DataStream 1 and so on


If you want to execute the print statement only one time (!) and on the last bar then this should work for you:

Code: Select all

Variables: NotExecuted(true);

if LastBarOnChart and NotExecuted then begin
NotExecuted = false;
Print....


If both solutions do not do what you are looking for, then I must have misunderstood your post and maybe you can clarify things a bit :wink:.

Best regards,

ABC

bowlesj3
Posts: 2028
Joined: 21 Jul 2007
Has thanked: 174 times
Been thanked: 405 times

Postby bowlesj3 » 08 Aug 2007

Hi ABC. Thanks. I learned a bit more about EL from your reply.

Here is a short description of what I am trying to get into MS-Access. Simply exactly the figures that are appearing on the Price Scale Column real time (for every last tick). Specifically [Price, upper/middle/lower band and RSI too]. My method worked but just gave MS-Access a lot more data than needed since it had to loop through all the older data to get the very last entry which was exactly what was appearing on that price scale real time. I should note that I have the bollinger bands set to "Update On Every Tick".

Regarding your suggestions, #1 would not work because the close of a 15 minute chart takes a long time to occur not to mention the 60 minute or daily chart.
Regarding your suggestion #2, the price was real time but the bands were not.

Atually if that print command would recreate the file every time that would probably do it. I need to scroung the manual to look for a delete command which will not stop the MultiCharts code if it does not find the file to delete.

John

bowlesj3
Posts: 2028
Joined: 21 Jul 2007
Has thanked: 174 times
Been thanked: 405 times

Postby bowlesj3 » 08 Aug 2007

Okay that was a fast scrounge. I just found this.
"You can use the reserve word "FileDelete" to simulate the behavior of the Print command".

I will try that but it begs the question "why is the print command appending to the file" or is it appending.

bowlesj3
Posts: 2028
Joined: 21 Jul 2007
Has thanked: 174 times
Been thanked: 405 times

Postby bowlesj3 » 08 Aug 2007

Its okay. My first method works and at least while running on the same machine the MS-Access processes that file so fast it appears to be operating almost instantly. Eventually this MS-Access program may run on a different machine where I may notice a bit of a difference for other reasons. Also when I get the same routine executing 8 times on 8 different files for every chart size it may add up to a bit of a slow down.

At this point wanting to know how to get only one line out to that file is more a matter of curiousity as to what is going in inside the logic of MultiCharts. By the way I put the fileDelete command in (see below) and it made no difference. That print statement is erasing the file every time it appears.

if LastBarOnChart then
begin
FileDelete("C:\Access\MC_BB_15.txt");
Print( File("C:\Access\MC_BB_15.txt"), Barnumber, "," , Close:8:5, ",", UpperBand:8:5, ",", Avg:8:5, "," , LowerBand:8:5);
end;

I always have found that the only way to learn a language is through experiments specifically designed to answer questions. It seems that when the study is first put in it starts to collect and keep in memory all the real time ticks that have come in from there on in (including the calcs) and the print command writes it all out to a new file every time. I say this because it seems to retain the ticks the the bar numbers back in time until when the study was first inserted in the chart. If I take the study out and put it back in it then clears that file. It may have to do with the logic of opening price and closing price for optimizing which I read about last night. That would make sense since it has to know where prices have gone I guess. In fact if it has a history of all ticks within the bar in the order that they came in it can do a very precise test.


John.

bowlesj3
Posts: 2028
Joined: 21 Jul 2007
Has thanked: 174 times
Been thanked: 405 times

Postby bowlesj3 » 08 Aug 2007

Maybe it is not when I should write the print statement but rather they way I limit the data going out for the variables. I say this because I just remember having come to the conclusion that in TS4.0 a varaible is not a single item in memory but rather an array that represents all bars on the chart and the [1] or [5] after the variable is a way of referencing that bar of the chart in the array. So I just tried [0] but I still get too much. It is the correct last figure but I only want 1 row.

if LastBarOnChart then
begin
Print( File("C:\Access\MC_BB_15.txt"), Barnumber[0], "," , Close[0]:8:5, ",", UpperBand[0]:8:5, ",", Avg[0]:8:5, "," , LowerBand[0]:8:5);
end;

I put it on the 1 minute bars rather than the 15 minute bars to get a faster update on what bar number is being put out on the file. I watched the chart to see a new bar being created and it added the 58434.00 to the file which is up one from the 58433.00 that it started out with when I first applied the study. The value I want is MS-access is the vary last row only. So it appears that the [0] has no effect at all in reducing the amount of data being written out to the file.


58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.50000,1504.79580,1499.07500,1493.35420
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.50000,1504.79580,1499.07500,1493.35420
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1493.00000,1504.70892,1499.10000,1493.49108
58433.00,1493.00000,1504.70892,1499.10000,1493.49108
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1493.00000,1504.70892,1499.10000,1493.49108
58433.00,1493.25000,1504.66785,1499.11250,1493.55715
58433.00,1493.25000,1504.66785,1499.11250,1493.55715
58433.00,1493.00000,1504.70892,1499.10000,1493.49108
58433.00,1493.25000,1504.66785,1499.11250,1493.55715
58433.00,1493.25000,1504.66785,1499.11250,1493.55715
58433.00,1493.25000,1504.66785,1499.11250,1493.55715
58433.00,1493.25000,1504.66785,1499.11250,1493.55715
58433.00,1493.25000,1504.66785,1499.11250,1493.55715
58433.00,1493.25000,1504.66785,1499.11250,1493.55715
58433.00,1493.25000,1504.66785,1499.11250,1493.55715
58433.00,1493.50000,1504.62841,1499.12500,1493.62159
58433.00,1493.50000,1504.62841,1499.12500,1493.62159
58433.00,1493.25000,1504.66785,1499.11250,1493.55715
58433.00,1493.25000,1504.66785,1499.11250,1493.55715
58433.00,1493.50000,1504.62841,1499.12500,1493.62159
58433.00,1493.50000,1504.62841,1499.12500,1493.62159
58433.00,1493.50000,1504.62841,1499.12500,1493.62159
58433.00,1493.00000,1504.70892,1499.10000,1493.49108
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1493.00000,1504.70892,1499.10000,1493.49108
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.50000,1504.79580,1499.07500,1493.35420
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1493.00000,1504.70892,1499.10000,1493.49108
58433.00,1493.00000,1504.70892,1499.10000,1493.49108
58433.00,1493.00000,1504.70892,1499.10000,1493.49108
58433.00,1493.00000,1504.70892,1499.10000,1493.49108
58433.00,1493.25000,1504.66785,1499.11250,1493.55715
58433.00,1493.00000,1504.70892,1499.10000,1493.49108
58433.00,1493.00000,1504.70892,1499.10000,1493.49108
58433.00,1493.00000,1504.70892,1499.10000,1493.49108
58433.00,1493.00000,1504.70892,1499.10000,1493.49108
58433.00,1493.00000,1504.70892,1499.10000,1493.49108
58433.00,1493.00000,1504.70892,1499.10000,1493.49108
58433.00,1493.00000,1504.70892,1499.10000,1493.49108
58433.00,1493.00000,1504.70892,1499.10000,1493.49108
58433.00,1493.00000,1504.70892,1499.10000,1493.49108
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1493.00000,1504.70892,1499.10000,1493.49108
58433.00,1493.00000,1504.70892,1499.10000,1493.49108
58433.00,1493.00000,1504.70892,1499.10000,1493.49108
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.50000,1504.79580,1499.07500,1493.35420
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.50000,1504.79580,1499.07500,1493.35420
58433.00,1492.50000,1504.79580,1499.07500,1493.35420
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.50000,1504.79580,1499.07500,1493.35420
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.50000,1504.79580,1499.07500,1493.35420
58433.00,1492.50000,1504.79580,1499.07500,1493.35420
58433.00,1492.50000,1504.79580,1499.07500,1493.35420
58433.00,1492.50000,1504.79580,1499.07500,1493.35420
58433.00,1492.50000,1504.79580,1499.07500,1493.35420
58433.00,1492.50000,1504.79580,1499.07500,1493.35420
58433.00,1493.00000,1504.70892,1499.10000,1493.49108
58433.00,1493.00000,1504.70892,1499.10000,1493.49108
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.50000,1504.79580,1499.07500,1493.35420
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.50000,1504.79580,1499.07500,1493.35420
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.50000,1504.79580,1499.07500,1493.35420
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58433.00,1492.75000,1504.75159,1499.08750,1493.42341
58434.00,1492.50000,1504.89980,1498.65000,1492.40020
58434.00,1492.50000,1504.89980,1498.65000,1492.40020
58434.00,1492.50000,1504.89980,1498.65000,1492.40020
58434.00,1492.75000,1504.86386,1498.66250,1492.46114
58434.00,1492.75000,1504.86386,1498.66250,1492.46114
58434.00,1492.75000,1504.86386,1498.66250,1492.46114
58434.00,1492.75000,1504.86386,1498.66250,1492.46114
58434.00,1493.00000,1504.82947,1498.67500,1492.52053
58434.00,1493.00000,1504.82947,1498.67500,1492.52053
58434.00,1493.00000,1504.82947,1498.67500,1492.52053

Guest

Postby Guest » 08 Aug 2007

If i understand you correctly, you want to do something, the first time a condition is met within a time bar right ?

then you can do this

vars: intrabarpersist alertBar(0);

if yourConditionIsTrue and alertBar<>barNumber then begin
// do what you want to do
alertBar=barNumber;
end

Hope this helps.. cheers

bowlesj3
Posts: 2028
Joined: 21 Jul 2007
Has thanked: 174 times
Been thanked: 405 times

Postby bowlesj3 » 09 Aug 2007

No actually I want to do it the very last time when the very last tick comes in which is within the bar (the very last row of data within the same bar number rather than the very first row of data within the same bar number). If you go back to my data which has the bar numbers on the left hand side notice that they repeat which shows that real time data is coming in over and over within that bar. I want the bottom row only which is the most recent tick within that bar. See next paragraph which I think is the actual solution come to think of it.

When a normal varaible in MultiCharts is defined it actually sets up an aray which is confirmed by the fact that you can put square brackets at the end of the variable to reference historic bar data (programmers who know other languages will realize that is true). The print statement is recreating the file each time but writing out all the intraday data within each bar in row by row format, not only from the last bar but also for all the bars that exist when the study is first applied to the chart (why it keeps sending out the old bars I don't understand because the condition statement should limit it to the last bar only). The solution I suspect will work in the end is on the next paragraph.

A global variable is one that, since it can be accessed directly by other applications, like maybe MS-Excel or MS-Access is not likely to automatically have an array set of for it (a theory at this point). Thus, if I set the global variables to the current price (which should be the last tick that came in) and also to the bollinger band calculated values and to the RSI value to, then that should (in my theory) be a single set of figures only (not an array). So at that point I would write out the global variables with the print statement which should write out only one line (the last row only). Not only that if I had MS-Access running on the same machine it should be able to (according to the people who sell products that provide global variables) directly pick up those global variables. Additionally I should be able to pickup the 15minute bar values directly in the 1 minute bar chart without having to worry about adjusting the bands in more than one place.

I will eventually test my thory. I have had a lot of experience programming in other languages. I would be surprised if this does not work.

What is confusing is why MultiCharts and TS do not have a built in global variable function just like MS-Access has with its public variables that can be accessed by any form or function. There is no limit to the number you can define in MS-Access. It begs the question as to why they did not design TS this way. The MS-Access public variables can not be shared across other applciations but if TS or MC could do this it wold be very helpful. It would make their product better and make it easier to sell it rather than having to go out and buy all these products to provide the global variable function. Maybe MultiCharts can do it and I have not found it yet. I don't think so though.

John.

bowlesj3
Posts: 2028
Joined: 21 Jul 2007
Has thanked: 174 times
Been thanked: 405 times

Partial work around.

Postby bowlesj3 » 09 Aug 2007

Direct to the solution.
I just tried something. If I go to my modified bollinger band study which I call "Bollinger Band Print Statement" (the one that writes out way more data than my MS-Access Program needs and thus ends up creating a very large file over the course of a day) and I use the Format Study screen to simply turn the study off this clears that file meaning it deletes it. Then when I turn the study back on it starts to create it again from the current bar at which it is turned on at. All I have to do is maybe at noon time each day remember to do this for every chart in my work space (a total of 8 or 9 charts). This will reduce the number of reads that MS-Access has to do to finally get to the very last row (or record as some may call it) to get the real time last tick value.

A work around until I determine if using global variables would work.

By the way another thing I may try is to define a function as simple rather than series and feed each value for the print statement thorough the function hoping that it will take in the series data and return only the simple value of the most recent tick thus making that print statement write out only one row rather than so so many rows.



John.

bowlesj3
Posts: 2028
Joined: 21 Jul 2007
Has thanked: 174 times
Been thanked: 405 times

Simple function does not work.

Postby bowlesj3 » 09 Aug 2007

Defining this function shown below with storage simple


inputs: MyValue(numeric) ;
BollingerSimple = MyValue;


and putting it in the print statement shown below

if LastBarOnChart then
Print( File("C:\Access\MC_BB_15.txt"), BollingerSimple(Barnumber), "," , BollingerSimple(Close):8:4, ",", BollingerSimple(UpperBand):8:4, ",", BollingerSimple(Avg):8:4, "," , BollingerSimple(LowerBand):8:4);

Does not solve the problem of getting more rows than needed during each print statement write and also the problem of getting more than the very last bar during every print statement write as shown by the very left most field which is the bar number (specifically notice it goes from 59313 to 59314 on the new minute bar).


59313.00,1483.7500,1484.6861,1481.1750,1477.6639
59313.00,1483.7500,1484.6861,1481.1750,1477.6639
59313.00,1483.7500,1484.6861,1481.1750,1477.6639
59313.00,1483.7500,1484.6861,1481.1750,1477.6639
59313.00,1484.0000,1484.7367,1481.1875,1477.6383
59313.00,1484.0000,1484.7367,1481.1875,1477.6383
59314.00,1484.0000,1484.9207,1481.4875,1478.0543
59314.00,1484.0000,1484.9207,1481.4875,1478.0543
59314.00,1484.0000,1484.9207,1481.4875,1478.0543
59314.00,1484.0000,1484.9207,1481.4875,1478.0543
59314.00,1483.7500,1484.8732,1481.4750,1478.0768
59314.00,1483.7500,1484.8732,1481.4750,1478.0768
59314.00,1483.7500,1484.8732,1481.4750,1478.0768
59314.00,1484.0000,1484.9207,1481.4875,1478.0543
59314.00,1484.0000,1484.9207,1481.4875,1478.0543

So maybe the global variable idea will not work (that being putting the values into global variables then feeding these global variables to the print statement hoping that will eliminate all the unneeded rows except the very last row which has the real time last tick values). I must say that the compiler that processes the EasyLanguage is a very interesting one and different than most I have been exposed to. I am not sure if it is worth putting much more effort (in time nor money) into getting only one row out since MS-Access runs very fast and gets to the last row real quick.

If someone wants to try it the script is near the top of this thread.

John.


Return to “MultiCharts”