Wealth-Lab Wiki describes a data feed called DDE (dynamic data exchange). Here is my understanding of it:
1min data in cloud <TCP> 1 min data provider app <DDE> Excel <?> WLD

Here is my understanding of the typical link to the data provider
1 min data in cloud <TCP> WLD
(I check the time stamp on the 1 min data file during streaming; it does not change until I exit streaming.)

Here is my question:
What is the link between Excel and WLD in the DDE case?
- Is Excel updated each minute and written to file?
- Or a memory copy of Excel data is provided to WLD also via DDE?

Why I am asking?
- WLP did not allow buying and selling of options
- I developed an app (RHS) that does this based on signals from WLP strategies

This app (RHS) also gets SPX quotes every few seconds.
So I have the ability to provide SPX 1 minute data to WLD if I know the protocol.

The easiest one would be WLD reading from an Excel or CSV file by hooking the file changed event. Hoping that this is how Excel communicates with WLD.

What gives me hope: in ASCII file data provider WLP reads an Excel file and then runs the strategy. It would be a step away to read from Excel file upon a file changed event.

I am trying to not pay a provider for one minute quotes on a single symbol (.SPX) especially since I have an app that produces this quote.

Thanks!

QUOTE:
- Is Excel updated each minute and written to file?

Provider communicates with the DDE server only. It doesn't care whether the workbook file gets updated or not.

QUOTE:
The easiest one would be WLD reading from an Excel or CSV file by hooking the file changed event. Hoping that this is how Excel communicates with WLD.

FileChangedEvent has nothing to do with how DDE operates. If you wish to learn more, the DDE provider is open source:

Home - MS123 Providers

If you can make your RHS app act as DDE server then the DDE provider could probably be used to stream live quotes into WLD.

Is WLD a DDE client in this scenario?
How does WLD communicate with Excel?

My RHS instead of replacing Excel as DDE server
could feed data into Excel as a DDE client.

Is this the data flow?
1 minute real time data in the cloud => TCP => provider app on my PC as DDE client => Excel as DDE server => WLD as DDE client

If so then I would replace it with:
RHS generates 1 min data as DDE client => to Excel as DDE server => WLD as DDE client

Is this correct?
Thanks!

.

Take a look at the online manual in the Wiki. You can specify your RHS app's DDE server name (excel I guess) and topic (whatever you're using, usually spreadsheet name) in the DDE streaming provider and try out how streaming quotes work from the RHS;

Excel/OpenOffice DDE Streaming Provider :: installation link
DDE streaming provider for Excel/OpenOffice :: Wiki manual (read before using)

Hi,

Moving from DDE to something I know more about: SQL server.

Read the Wiki write-up on the Database provider. Looks great.

One question:
In "Setting up streaming quotes":
"For streaming quotes, only the Close price is required"

Does this mean only the close price for formatting purposes?
Or it means you only use the close price for 1 minute data?

I would hope for each minute OHLC would be available.

Thanks!

Hi,

This means the Database streaming provider polls your DB for tick updates - to differentiate from what is called mini-bars (which streaming providers may support too). [Those OHLC mini-bars are used by some vendors to return snapshot quotes.] Actually, the DB provider isn't using "true" tick updates: it polls your database for real-time quotes twice every second instead. But for most needs this can be considered tick updates. So the answer is yes, 1-Minute OHLC will be available with this provider.

Your sample code in the Wiki has: DateTime _dt = DateTime.Now;
C# format is: "7/19/2020 9:16:37 PM".
T-SQL has for DATETIME:: "20160212 12:30:15.123"
or SMALLDATETIME "20160212 12:30'

Which format should I use in a database row so that WLD accepts it?
Thanks!

Whichever time format you choose (datetime or datetime2), .NET should take care of it automatically.

Hi,

I wrote the code for RHS to write 1 minute DOHLCV to a SQL DB table.
And tested it and verified it using SSMS.

There is up to 3 seconds of jitter.
1 minute data is ready at top or minute or 3 seconds later.

Here is my question:
- You mentioned that WLD checks the SQL DB a couple of times a second
- What does it look for to detect change?
- That the previous minute was 9:54 and this one 9:55?
- Does it makes sure the time data is updated is exactly at top of minute?

Hi,

It may make sense. The PC clock must be synced and timestamps in your DB must be aligned. 3 seconds of jitter shouldn't be an issue for the Streaming provider which allows up to 5 seconds to adjust for slightly incorrect local time.

Hi Eugene,

Can you please tell me if the following reasoning is correct:

It seems SQL Server 2019 does not store rows sequentially.
There is no "give me the last row" command that WLD uses

As a result WLD needs to know the time
and at the top of the minute
it searches for a row with today's date and current time, say, 9:41
or a row with time a little before 9:41 and a little after

This is why you are saying we have a 5 second time window.

In my case both the SQL writer (RHS) and reader (WLD) are on the same machine
But they do not need to be

Thanks!
Hal

Hi Hal,

As databases can be unordered, the DB provider doesn't ask for the "last row" but instead queries the range of rows that falls within a starting and ending DateTime (ordered by date).

Having a problem with connection string.

The following connection string works in my C# RHS app:
string connectionString = "Server=HP-2013;Database=RHSSPX1MDB;Trusted_Connection=True";

Here is some data this app wrote to the 1 minute real time SQL table this morning:
SPX 2020-07-29 12:58:01.287 3247.75 3247.9 3246.95 3247.4 1001 0
SPX 2020-07-29 12:59:01.127 3247.39 3247.58 3247.27 3247.46 1001 0
SPX 2020-07-29 13:00:00.907 3247.44 3247.49 3247.22 3247.46 1001 0
SPX 2020-07-29 13:01:00.757 3247.42 3248.22 3247.41 3248.06 1001 0
SPX 2020-07-29 13:02:00.970 3248.14 3248.17 3246.96 3246.96 1001 0
SPX 2020-07-29 13:03:00.907 3247.08 3247.22 3246.58 3246.93 1001 0
SPX 2020-07-29 13:04:01.817 3247.02 3247.72 3247.02 3247.47 1001 0

When I use the same connection string in the WLD data Manager using SystemData.SqlClient
I get the error: Keyword not supported "server". A snip of this is attached.

My RHS app has the following using statements to support SqlClient:
using System.Data;
using System.Data.SqlClient;
using System.Globalization;

I can add these lines to my 1 minute and day strategies. But most likely Data Manager does not look there.
Your help is appreciated. No hurries as my after hours tweaks with ASCII csv day and minute data are working.

Thanks,
Hal

I believe there's no need in quotes nor ";". As can be seen on an image in the online user guide, the connection string should not be wrapped in quotes as you put it in the field:

"Server=HP-2013;Database=RHSSPX1MDB;Trusted_Connection=True";
Server=HP-2013;Database=RHSSPX1MDB;Trusted_Connection=True

thanks a bunch that worked!!!!!

Glad to help!

Sorry for too many calls but once I get going it will be maintenance free.

I was able to create two data sets, both 1 minute, one static and the other for real time purposes..

Next step was per Wiki: to choose Data Manager / Database provider tab
to see the "Setup Streaming Data" panel. Got there.

If I click "Connection string help" it tells me "select a provider" first.

But the select provider pull down does not have any options (in create data set I could see many options)

I tried to enter the same connection string that worked for creating data sets: no response.

Also tried to reverse steps and pick database in preferences / streaming.

Think I am one or two baby steps away from resolution.

I can see my posts from my profile (underlined haytac) as you suggested.

Thanks,
Hal

QUOTE:
Think I am one or two baby steps away from resolution.

Right, you'll find a solution after reading and following the Notes right below "Setting up Streaming quotes".

Hi Eugene,

To my amazement the database provider works in streaming mode.
I have two different data bases:
- one for static 1 minute data up to the previous session's 4:00 pm
- another generated by my RHS app starting with 9:31 am of the current session

There are two issues:

a) Although my SQL database provides OHLC WLD only uses Close
This is for continuously generated session 1 minute data
I can see full 1 minute candlesticks on static data

What WLD does is assigns Open, High, Low all to Close.to create a Bars object

I can see that this is indicated in the Wiki write-up.
My strategy uses all four values OHLC.

It would be great if you could add OHL.to Bars in streaming mode.

b) WLD skips the 9:31 run although SQL data was ready at 9:31:01
WLD creates a Bars that is correct but is it 60 seconds late.

Looking at the price pane, there is no data at 9:31.
The 9:32 candlestick is based on SQL 9:31 close data.

Hope there is a resolution to both issues.

Thanks a bunch,
Hal

Hi Hal,

Great to hear it works for you!

a) I don't remember why but the streaming provider has to stay Close only. Maybe to be universally compatible with more databases which not necessarily have to keep OHL.

b) Maybe the effect of Market Manager's filter for market hours, not sure.

Hi Eugene,

Is there a way to proceed to a next step on these two issues?
Do I fill out a ticket?

These limitations take away from the effectiveness of my 1 minute strategies significantly.
They are reasonable features as well.

Thanks,
Hal

Re: #1, you're talking about something else than I had in mind. My point was that for WL it's enough to have the Close price only from the streaming DB provider to build streaming OHLC bars but this isn't relevant in the context of your issue.

Having re-read your message, problem is that OHL=C. I don't see any glaring bugs in the streaming provider's code and nobody reported this before. I think this may have to do with timestamps in your database. Can you attach a screenshot of the provider in action and a snippet of your streaming database?

As for #2, I gave you a pointer that is the Market Manager. Have you already reviewed the User Guide chapter?

Thank you for your time!

Yes, I build 1 min OHLC from .SPX quotes every 2 seconds with the RHS app.
This is then written to a SQL Table to be read by WLD.

Will follow up on your inputs and get back to you in a couple of days.

Hi Eugene,
My stock name "SPX" is not included in Market Manager.

I have been doing tests on the three issues I had:
a) WLD streaming does not run at 9:31ET
b) It runs at 9:32 and after but uses data from a minute earlier
c) WLD in streaming mode uses C for OHLC

These issues did not go away but I gathered a lot of data and eliminated a number of possbilities.

i) As you had mentioned WLD in streaming mode seems to check for new data in SQL DB. If there is new data it starts after about one second. As mentioned above it does not use the new data however. It uses data from 60 seconds ago.
Example: OHLC shows up at SQL at 06:33:55.830 PT
(it is labeled: 9:31:00)
WLD runs at 56.846 a second later

I also tried data right after top of minute. It did not change results.

ii) A very interesting fact is that WLD seems to capture all of OHLC. How do I know this? If I stop streaming WLD runs one more time. And in that run one sees the latest data in full. Moreover the price pane now displays correct OHLC from the start of the day at the correct time.
I do not know if WLD reads SQL from the start of the day in rhe run right after streaming stops or WLD has cached OHLC data. In any case it is there.

Last streaming pass at 9:53:58 ET data: 3382.50 for OHLC. Checking SQL with SSMS this data was collected at 52:57 a minute earlier.

After streaming stops there is one last run at 9:54:7 ET.
WLD shows on the price pane: 3381.56, 3382.34, 3380.85, 3381.93
Inspection of SQL with SSMS shows that this data is from 9:53:57.

iii) Please note that on my WLD the data manager database provider tab opens up a small window to provide information for streaming data provider. There is only a single entry for data and that is Close. So C=OHLC for streaming is there by design. It would be very nice if this was changed to take in all of OHLC. Which WLD seems to do anyway as shown by the run right after streaming stops.

The top priority issue is WLD using 60 second old data in streaming mode.
The second is that it skips the 9:31 run
The last one is WLD using only Close

Your help in these matters is much appreciated.

Hal

one more attachment

1. Looks like this is by design. Wealth-Lab uses end-of-bar time labeling. Consequently, a quote with 'dtSQL' timestamp of 09:27:00.963 will appear in the 09:28 bar. Same for the 09:31 date time which starts to build the 09:33 bar.

2. I don't know what you're talking about re: C=OHLC. The provider has been tested with different databases, correctly displaying the partial bar's range as OHLC. WL builds the bar tick by tick (actually, in snapshot mode but polling your DB twice a second) and should show the full OHLC range on the chart.

On 1. I can produce OHLC at any time during a minute (dtSQL) and label it (DateTime) any way that is desired.
What do you recommend I do? Data that is 60 second late is a show stopper.

Sounds like if you shift it back 1 minute, that would possible eliminate the delay. Give it a try.

Thanks, will try.
In a way I tried it already.
One can stop streaming and then restart again. In this case it does one last run.
Each minute I run the bars from the beginning of the day to the current minute.
I stopped streaming at 10:09

Numbers from that last run at 10:09:18
<CSOpen>3382.38</CSOpen>
<CSHigh>3382.86</CSHigh>
<CSLow>3382.16</CSLow>
<CSClose>3382.59</CSClose>

Then I click stream again so this run has all previous minutes as recorded into a file plus this minute
Stream run at 10:09:57
<CSOpen>3382.38</CSOpen>
<CSHigh>3382.38</CSHigh>
<CSLow>3382.38</CSLow>
<CSClose>3382.38</CSClose>

Note that it picked the Open from Bars at 10:09: 3382.38
Reason: by mistake I picked Open in DB data provider stream setup for Close.

The data fir the 10:09:18 run either came from SQL or WLD had it in memory.
It is correctly labeled and has all four distinct components.

The moment stream starts and it starts when new data arrives
WLD is back to picking the earlier data (Open).

So 3382.38 is used twice.

Looks like WLD sqlClient has two things it does
a) Here is new data: better start a run (maybe tracking count of rows today)
b) then pick earlier data (SQL statement?)

It would be good to know SQL statements for (a) and (b).

Will try what you suggested this coming Monday.

My hit ratios from after hours back test runs have been high 30%.
Great motivator. Can't wait to get back in the game again.

Thanks!

One other thought is the shadow bar for streaming that you introduced a while back.
I am currently not providing WLD info for shadow bars.
I could be feeding OHLC to SQL Server 2019 every few seconds
This would be info for a shadow bar.
Then maybe WLD will pick the correct OHLC for top of minute run?

Is there a protocol to feed the shadow bar.

.

Starting to see why the DB data provider just has Close.
It expects that WLD will build OHLC from Close data over multiple seconds during the span of a minute.
Over the weekend I will change code so SQL Server 2019 is inundated with Close data every couple of seconds.

1) When Streaming starts or finishes, WL will reload the static data for the instrument to display it on the chart. In your case the provider uses the connection string you supplied when setting up the static (historical) data. As this database table gets periodically updated you may see some difference between the last streaming bar (in progress) and the static chart (when it gets refreshed from your "static" table).

2) The partial OHLC bar is based on Streaming data so no protocol.

3) Right on the money re: Close-only streaming data.

Progress to report (a,b) and an issue (c)

a) I start letting RHS write to SQL at 9:30:02 about every 2 seconds
- WLD builds OHLC
- WLD starts running at 9:31
- In stream mode I can see OHLC
- also see what you call the shadow bar
- this is all very nice back to Fidelity days
- thanks a bunch for support!

b) stop stream and then restart
- I need to be building my 1 minute static table at the top of the minute all day long using RHS
- in summary RHS feeds one 2sec table for WLD to build 1 min OHLC
- RHS also feeds the 1 minute 10 day static table with 1 minute intraday data
- in case WLD streaming needs to be stopped and then restarted

c) WLD ran for 37 (7:07 PT) minutes and then stopped streaming
- at 7:10 PT WLD shut itself down
- checked SQL data (the every 2 sec data) looks fine
- Any guesses as to what may have happened is appreciated?

Thanks!

It's great to hear you're having progress.

c) Apparently there was an unhandled exception in your streaming Strategy or maybe in the DB provider. The provider does not do logging so even if you enabled it in WLD that won't help much if it was its fault, I assume.

Hi Eugene,

Did more detective work on the WLDev shutdown issue.
The problem happens right after a successful 1 minute streaming run where the start time has the largest time value in seconds and milliseconds so far in the day.

WLDev starts last run at 7:48:04.271 and ends it at 7:48:05.204
At 7:49 WLDev is alive but does not do a 1 minute streaming run
Right after it shuts down

The top line here has the SQL write time for the last successful 1 minute streaming run.
2020-08-24 07:48:03.067 largest time for first SQL write time at top of a minute
2020-08-24 07:49:02.730
2020-08-24 07:52:02.233
2020-08-24 06:37:02.203
2020-08-24 06:38:02.000

Timestamp from RHS SQL write time
First SQL write in new minute 2020-08-24 07:48:02.460 2020-08-24 07:48:03.067
The write before that 2020-08-24 07:47:59.883 2020-08-24 07:48:00.257

I established that WLDev uses all values timestamped inside minute 7:47 to construct OHLC.

It then waits for the first SQL write in the new minute before starting the run. This first SQL write is not used for OHLC for this run. But waiting for the first SQL write in the new minute proves that the last SQL write was the last in the previous minute used for this run.

So somehow starting the run at second 4 has to do with the next run not happening and the subsequent crash.

I can artificially timestamp SQL writes very close to second 60 with a value over 60 so that WLDev takes that write as a cue to start the run. But it is likely that in some cases the artificial timestamp value will be larger than the SQL write time. I do not know if WLDev checks for that.

Any inputs you might have on the workings of WLDev DB data provider are appreciated.

Thanks!
Hal

Hi Hal,

In the Database provider, the client that makes static and streaming data requests is protected from unhandled exceptions and shouldn't crash. But I cannot exclude the possibility of a specific condition in your DB setup that breaks the streaming provider.

Since I have no idea what code is being run when WLD crashes (and as we know it's quite lengthy), your best bet is to run your Streaming strategy under Visual Studio debugger. This way you may have more insight into the problem.

Hi Eugene,

After many years of resisting it (and so far cutting corners worked) will be doing the following:

a) using thread "accessing wealth lab error log" have a look at wld.txt file
- i made the changes to wld exe config as attached
- do not see a folder data/logs (search at top level for wld.txt turned up nothing)
question: I did an after hours run for the 1 minute strategy do i expect to see this file somewhere?
question: is it created when there is an error or always?
question: assume it is for any run of any strategy

b) using Dion Kurczek write-up on using Visual Studio 2019 for debugging my strategy
- still in prep stage

Appreciate your comments on (a)

Thanks,
Hal

Hi Hal,

a) Firstly, I cannot give any promise that the log might be useful for troubleshooting your crashing strategy window.

Also I wouldn't put too much faith in it since the DB provider doesn't add anything to that log. Only a handful of providers may be doing it, Yahoo in the first place. The log is actually being written to in 6.9.23 (i.e. outside the Data folder):

c:\Users\Windows username\AppData\Roaming\Fidelity Investments\WealthLabPro\1.0.0.0\Logs\wld.txt

The solution in (b) would definitely be useful. Even when you face the scenario when little details are provided in VS as its the external code (DB provider or WL itself) that may be crashing. That's still a good pointer for further troubleshooting.

Hi Eugene,

This is regarding WLD shutting down between one to two hours into a streaming session with data provided from a SQL Server 2019 as follows:
- Static minute OHLC data in one table and in another table:
- Real time 4 second data fed to SQL Server from my program (Fidelity quotes)
- Both tables are in the same database

As you suggested I brought up Visual Studio 2019 to debug this problem.
- Created a blank strategy using “How can I debug my strategies in WL” by Dion Kurczek and using Visual Studio 2019. Following all the steps I was able to run this strategy in minute or day mode. Basically the price pane just shows the candlesticks. I was able to run it in streaming mode as well.
- Copied all stubs from the thread: “Using Visual Studio 2015 and 2017 for debugging”. I appreciated learning this as it might come in handy in the future.

This blank strategy also failed. This time at 73 minutes.

The benefit of Visual Studio was WLD created a break caught by Visual Studio.
The snips of all this information are attached.

The error message was:
System.NullReferenceException: “object reference not set to an instance of an object”
“your app has entered a break state, but there is no code to show because all threads were executing external code (typically system or framework code).

The strategy’s MyStrategy class has nothing in it. It seems that the 4 second Close data that I am writing to the SQL table that WLD reads has a mismatch of some kind to the WLD code that is reading this data.

I think you call this piece of code “Database Provider”. You had mentioned earlier that this code checks the SQL table a couple of times a second. I am wondering if there is a write (from my code) read (WLD database provider) collision that is random. I do not know how SQL Server 2019 behaves in this situation?

If there is a more detailed description of WLD code in this area that would be helpful. There is some jitter in the system timer but I can change the timing of the writes.

It would be very nice if someone on your end reviewed the DB provider code for possible reasons. Or if you could provide the source for this piece of code under NDA I can see where things go wrong.

Thanks,
Hal

Hi Hal,

I will create a beta version of the Database provider in attempt to fix the issue you're having. Please create a support ticket.

Thanks a bunch Eugene! Just created a support ticket for this issue. Hal

Got it. Looking forward for your testing results.

@haytac

The WL crash in Streaming mode is fixed in version 2020.10 (just released). Thanks for reporting the bug, providing detailed bug reports and beta testing!