troubleshooting Question

Convert C# .NET or VB.NET to Excel - PLEASE

Avatar of cpatte7372
cpatte7372Flag for United Kingdom of Great Britain and Northern Ireland asked on
C#Visual Basic.NETMicrosoft Excel
70 Comments1 Solution419 ViewsLast Modified:
Hello Experts,

I would be very appreciative if someone could show me how to convert the following program into Excel, either using conditonal formatting or VBA. The program is an indicator that is used by Traders to make money on the stock market. Its called Trade Volume Index.

The following is a description of what the Trade Volume Index program sets out to achieve.

After the description I have provided the code used in C#.NET to compile the formula and another formula coded in VB.NET.

Hopefully, someone with experience in any of those languages with experience in Excel will be able re-code the program into Excel??

Trade Volume Index Definition

The trade volume index (TVI) detects whether a security is being bought or sold based on tick data.  The TVI provides a trader more insight into the amount of buying and selling for a security.  It tracks the total volume that occurs at the bid and ask.  So, if the trade volume index is rising, meaning more people are buying at the ask and the price of the stock is rising, one can assume the uptrend has legs.  Conversely, if the trade volume index is falling and the stock is dropping like a rock, then a stronger downtrend is in play.
Who is using the Trade Volume Index

The trade volume index is used primarily by day trading professionals.  This is because active traders are most concerned with how stocks perform at key levels and have to make swift decisions.  Long-term investors are less concerned with intraday data and focus their attention on how a stock closes at the end of the day.
How to use the Trade Volume Index

The TVI shows its predictive power when assessing a stock that is flat lining at a particular level.  How many times have you been watching a stock at a particular level and wonder whether it has the juice to get through a certain level.  The trade volume index will peel back the onion and show you what traders are doing.  For example, if you want to buy a stock on a break of $100, and it has been flat lining for 2 hours, you may hesitate on pulling the trigger due to the flatness in the market before the breakout.  However, if you see that the TVI has been rising over this 2-hour period, it is a sign that traders are accumulating the stock at the ask price, thus increasing the odds that the stock will have legs when it clears resistance.

How to Calculate the TVI

The trade volume index is calculated by using the following formula

MTV = Minimum Tick Value

Change = Price minus the extreme price since direction changed

If Change is greater than MTV, then Direction = Accumulate

If Change is less than MTV, then Direction = Distribute

If Change is less than or equal to MTV and Change is greater than or equal to MTV, then Direction = Last Direction

Lastly, we must calculate the TVI, which is simple once you know the Direction.

If Direction is Accumulate, then TVI = previous TVI + Volume

If Direction is Distribute, then TVI = previous TVI – Volume

I have attached a simple spreadsheet with the values for illustration.

Now, for the coding.
The first in C#.NET

 /// <summary>
        /// Called on each bar update event (incoming tick)
        /// </summary>
        protected override void OnBarUpdate()
        {
                  if(CurrentBar <= 1)
                        return;                                    
                  
                  if(Close[0] - Close[1] > TickSize)
                        dir = 1;
                  if(Close[0] - Close[1] < -TickSize)
                        dir = -1;
                  
                  if(dir == 1)
                        vTVI += Volume[0];
                  if(dir == -1)
                        vTVI -= Volume[0];      
                  
                  PLOTTVI.Set(vTVI);
        }

        #region Properties

        [Browsable(false)]  // this line prevents the data series from being displayed in the indicator properties dialog, do not remove
        [XmlIgnore()]       // this line ensures that the indicator can be saved/recovered as part of a chart template, do not remove
        public DataSeries PLOTTVI
        {
            get { return Values[0]; }
        }

        #endregion
    }
}

Now for VB.NET, please view the following link.

http://www.iqbroker.com/technical-indicators/trade-volume-index.aspx?id=Indicator_1901

If one of you Experts can find solution, I won't be able to express how grateful I would be.

Cheers

Carlton
sample.xlsx
ASKER CERTIFIED SOLUTION
rspahitz

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 70 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 70 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros