Hello... I have a requirement and want to know if it is possible to do it somehow (via ninjascript/c# or anything for that matter!! )... So... here's the idea...

In Ninjatrader, I will draw trendlines going up and down like this...



In Excel 2016, I have data like this...



As you know... each HIGH/LOW a trend line is attached to.... happens on a particular date.... and... that Hi/Lo day has an extreme price attached to it (which is nothing but the High price and Low Price).

As I draw these trendlines, I want excel to recognize the High or Low "Date" from where a trendline started or where it ended and automatically highlight the "High" price in Green and "Low" price in Red...for those particular days... in the spreadsheet... I want this to happen in real-time or with a delay of say 4-10 sec... I'm okay with that...

For the above trendlines I drew, the result would be... like this...



I want this to happen in real-time. As I keep drawing new trend-lines in Ninjatrader, corresponding High and Low prices should be highlighted automatically in Excel spreadsheet....

Please tell me how this is achievable... Is it complicated ?? also... please suggest a programmer or service that I can use to achieve this....I wont be programming this myself... but I am ready to hire a programmer if at all this is possible to do... I feel somehow.. that it is not impossible to achieve...

This is a fairly easy requirement. This can all be achieved used the conditional formatting section with an extra 2 columns and a few other basic formulas:

What you would need to do is use conditional formatting in connection with a new column that strictly displays the max and min based on whatever period of time you want. This could be the entire data set from the begining of time to the end of your data set or it could be based on a date or time field. If it is for the entire data set you just run the formulas:

First the 2 new columns:


=Max(range)
=Min(Range)
where range = the entire columns C for max and D for Min. If you need to deliniate the periods in any way, you just add an if statement to the max / min formula. Such as

= if(condition = true, max(range), "") The condition you could check may be something like, the day, or hour from the current row = the day or hour from the previous row. This will partition your data into how ever many groups you want.


Next the conditional formatting: Home > Conditional Formatting > Highlight Cells Rules

Here you just hop over to the High and Low columns and on the high cells run the fomula that compares each cell to the max from the new max column. If these two are = then highlight green, if not do nothing.
Then just run the opposite for the lows.

Easy to handle the cosmetics at least

Getting an API to feed excel via NinjaTrader in real time is the tricky part. I wrote a VBA script back in the day to get real time updates from yahoo financial. Not sure if this is even a thing anymore. LOL.


Hope this helps with at least some of the heavy lifting.


Ian

Hi...

As I told earlier... I have no frickin idea how to do this on my own...

Although I know some good functions like vlookups in excel, I have no idea how to make two software speak with each other... That's what I'm trying to do here..

The most important thing here is... "convenience"... I dont want to touch anything in excel.... no formulas nothing... I will only draw trendlines in Ninjatrader and I want excel to pick those things up and show me the highlights... THAT'S IT...

Dont want to deal with the problem "using formulas..." It must be dealt with using APIs (as you said) and must be "automatic"

I feel your pain, and you will definitely need a programmer for this. As it takes many steps. There may be some 3rd party stuff out there for sale, but I haven't checked. But since I have already done something similar I can give a few ideas.... (Kind of a down and dirty way to do it all, but it definitely will work and not break the bank)


Here is how you would do it:

1. Build a Ninja-trader strategy to run concurrently with what you are doing live. Create a sub routine for your manual clicking and capture your actions in this subroutine.

2. Create a few variables to capture the data live such as High[0], Low[0], Open[0], Close[0] and anything else you want. Pretty easy stuff.

3. Create a sub routine that sends the data on each bar update to a SQL Server via an insert statement. I have already built such a program. I could share some of this type of code if you or any programmers you work with were interested.

4. In SQL you can create views of the data to have it only look at a specific subset of data based on time. Let's say check the computers internal clock against the data stamp of the NT data and only display records that are < 10 seconds old. You can define this however like, but you need some logic to partition this raw data into batches that you will be sending back to excel, otherwise it will just be the entire table each time. Easy to do though.

5. In excel you just setup a standard data source connection to your SQL server view that has the data in time defined batches and from here you can get the data a number of different ways.

6. The best method to get the data to sync with your batches is to set a counter sub in VBA that runs a time based bool condition and every 10 seconds or whatever it simply refreshes the data source to go fetch the new data.

7. In terms of putting the data into one unabridged data-set in excel I would have one tab that just has the connection to the SQL view and keeps refreshing, but you would need a second tab to hold all of the unabridged data. You can do this in two parts.

A: For each column, just have a basic formula that points to the matching column from the data source connected to SQL. This will theoretically run thousands of rows, whereas the data connected to SQL may only be hundreds or rows or less. With each refresh it would theoretically just be replacing the old values with the new values so you need some logic to kill the formulas on the rows that just took the update.

B: Create a Second VBA sub to capture the rows that just recently took the update and kill the formulas rendering these as values, There are a number of ways to do this, but this is the tricky part I believe.


Overall not the easiest thing to do, but I actually built something like this back in the day. This may be useful if you can find a programmer to help you.

Best of luck.

Ian

OH MY GOD!! It sounds too complicated... but hey... I wont be writing any strategy/code... I'll just pass on these ideas to a coder and see if he can come up with something to work out...

Thank you so much for taking the time to share your thoughts!!! I'll give this idea to a programmer and see if they can do it...