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

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

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

C#Visual Basic.NETMicrosoft Excel

rspahitz

Thanks for responding mate.

Do you copy and paste the code in a VBA module?

Thanks for responding mate.

Do you copy and paste the code in a VBA module?

rspahitz

I'm not entirely sure what you mean.

Any chance you could provide an example?

Cheers

I'm not entirely sure what you mean.

Any chance you could provide an example?

Cheers

Did you mean copy and paste the following:

' Get the volume of the symbol, indexed by bar index.

Define volume() As Number = BarVolume(_symbolIndex, barIndex, length + 1)

' Get the closing prices of the symbol, indexed by bar index.

Define close() As Number = BarClose(_symbolIndex, barIndex, length + 1)

' Use for the latest trade volume index.

Define TVI As Number = IndicatorIndexValue()

' Use for the calculated indicator script values, indexed by bar index.

Define results(length - 1) As Number

' Calculate the indicator script values for the specified bar range.

For i As Integer = length - 1 To 0 Step -1

' Calculate the indicator script value for the current bar.

If (close(i) - close(i+1) > _MTV) Then

TVI = TVI + volume(i)

Else

TVI = TVI - volume(i)

End If

results(i) = TVI

Next

Return results

Cheers

' Get the volume of the symbol, indexed by bar index.

Define volume() As Number = BarVolume(_symbolIndex, barIndex, length + 1)

' Get the closing prices of the symbol, indexed by bar index.

Define close() As Number = BarClose(_symbolIndex, barIndex, length + 1)

' Use for the latest trade volume index.

Define TVI As Number = IndicatorIndexValue()

' Use for the calculated indicator script values, indexed by bar index.

Define results(length - 1) As Number

' Calculate the indicator script values for the specified bar range.

For i As Integer = length - 1 To 0 Step -1

' Calculate the indicator script value for the current bar.

If (close(i) - close(i+1) > _MTV) Then

TVI = TVI + volume(i)

Else

TVI = TVI - volume(i)

End If

results(i) = TVI

Next

Return results

Cheers

OK, I guess not exactly copy-paste.

In VBA, functions are defined the same way as in VB.Net, but the return value is the name of the function rather than the Return keyword.

There are a few problems with the code you provided, mainly that it is not VB.Net code.

I suspect that a better translation would be:

Translating this in VBA is now a breeze:

In VBA, functions are defined the same way as in VB.Net, but the return value is the name of the function rather than the Return keyword.

There are a few problems with the code you provided, mainly that it is not VB.Net code.

I suspect that a better translation would be:

```
Function TVI()
' Get the volume of the symbol, indexed by bar index.
Dim volume() As Double = BarVolume(_symbolIndex, barIndex, length + 1)
' Get the closing prices of the symbol, indexed by bar index.
Dim close() As Double = BarClose(_symbolIndex, barIndex, length + 1)
' Use for the latest trade volume index.
Dim TVI As Double = IndicatorIndexValue()
' Use for the calculated indicator script values, indexed by bar index.
Dim results(length - 1) As Double
Dim i As Integer
' Calculate the indicator script values for the specified bar range.
For i = length - 1 To 0 Step -1
' Calculate the indicator script value for the current bar.
If (close(i) - close(i+1) > _MTV) Then
TVI = TVI + volume(i)
Else
TVI = TVI - volume(i)
End If
results(i) = TVI
Next
Return results
End Function
```

Translating this in VBA is now a breeze:

```
Function TVI()
' Get the volume of the symbol, indexed by bar index.
Dim volume() As Double = BarVolume(_symbolIndex, barIndex, length + 1)
' Get the closing prices of the symbol, indexed by bar index.
Dim close() As Double = BarClose(_symbolIndex, barIndex, length + 1)
' Use for the latest trade volume index.
Dim TVI As Double = IndicatorIndexValue()
' Use for the calculated indicator script values, indexed by bar index.
Dim results(length - 1) As Double
Dim i As Integer
' Calculate the indicator script values for the specified bar range.
For i = length - 1 To 0 Step -1
' Calculate the indicator script value for the current bar.
If (close(i) - close(i+1) > _MTV) Then
TVI = TVI + volume(i)
Else
TVI = TVI - volume(i)
End If
results(i) = TVI
Next
TVI= results
End Function
```

The remaining problems are:

1) TVI is defined twice (once as the function name and once as the 3rd variable...easy enough to cahnge the 3rd variable and all references except the last to something else, or to change the function name and the reference in the last line.

2) The first three lines refer to either functions or arrays that are currently undefined. If they're functions, those will have to be added to return a double-data-type array; if they're arrays, their values will have to be defined somewhere.

3) the variable "length" is also undefined.

Other than that, you should be ready to go. Place this code in a new module in Excel's macro area. Simple way to do that from Excel:

* Alt-F11 to open the VBA area

* menu Insert | Module

* Paste the correct code into the window.

Back in excel, change a cell so that the formula is:

=TVI()

and you're done.

Oh, you probably want the function to receive incoming values (parameters) which will require knowing what the function is supposed to receive and how those parameters are supposed to be used.

1) TVI is defined twice (once as the function name and once as the 3rd variable...easy enough to cahnge the 3rd variable and all references except the last to something else, or to change the function name and the reference in the last line.

2) The first three lines refer to either functions or arrays that are currently undefined. If they're functions, those will have to be added to return a double-data-type array; if they're arrays, their values will have to be defined somewhere.

3) the variable "length" is also undefined.

Other than that, you should be ready to go. Place this code in a new module in Excel's macro area. Simple way to do that from Excel:

* Alt-F11 to open the VBA area

* menu Insert | Module

* Paste the correct code into the window.

Back in excel, change a cell so that the formula is:

=TVI()

and you're done.

Oh, you probably want the function to receive incoming values (parameters) which will require knowing what the function is supposed to receive and how those parameters are supposed to be used.

Rspahitz

If this works I won't be able to thank you enough. Seriously mate, thank you.

Going to check it out now.

If this works I won't be able to thank you enough. Seriously mate, thank you.

Going to check it out now.

Rspahitz,

Just so I'm clear. Am I just required to place the code in the macro area? The steps you mentioned a just for informational? If not I wouldn't have a clue how to add a function to a return-data-type array.

Cheers

Just so I'm clear. Am I just required to place the code in the macro area? The steps you mentioned a just for informational? If not I wouldn't have a clue how to add a function to a return-data-type array.

Cheers

rspahitz:

I'm getting the following syntax error:

Function TVI()

I'm getting the following syntax error:

Function TVI()

The error also points to:

Dim volume() As Double = BarVolume(_symbolIndex, barIndex, length + 1)

Cheers

Dim volume() As Double = BarVolume(_symbolIndex, barIndex, length + 1)

Cheers

As indicated, you have a few undefined areas to be resolved. Lets go through the pieces one at a time.

Start with this, just so you understand how it works.

Comment out everything in the function so all you have left is this:

Function TVI()

' comment out the rest of the code

TVI=123

End Function

go to Excel and add a formula:

=TVI()

You should get the value 123; if not then the function is in the wrong place.

Start with this, just so you understand how it works.

Comment out everything in the function so all you have left is this:

Function TVI()

' comment out the rest of the code

TVI=123

End Function

go to Excel and add a formula:

=TVI()

You should get the value 123; if not then the function is in the wrong place.

The next step is to build that up and do things like properly define the function by returning a correct value:

Function TVI() As Double

TVI = 123

End Function

Function TVI() As Double

TVI = 123

End Function

Now some questions:

1) Why is "results" defined as an array? What are you expecting to return to an Excel formula? typically you return a single value to display. If you really want an array, maybe you don't want an Excel function but rather a VB function that feeds info to a VB sub to do something for Excel.

2) What are the definitions for the following?

BarVolume(_symbolIndex, barIndex, length + 1)

BarClose(_symbolIndex, barIndex, length + 1)

IndicatorIndexValue()

1) Why is "results" defined as an array? What are you expecting to return to an Excel formula? typically you return a single value to display. If you really want an array, maybe you don't want an Excel function but rather a VB function that feeds info to a VB sub to do something for Excel.

2) What are the definitions for the following?

BarVolume(_symbolIndex, barIndex, length + 1)

BarClose(_symbolIndex, barIndex, length + 1)

IndicatorIndexValue()

General comment: I believe the refernced code is for an event handler.

I believe the array is used to plot the activity of the symbol.

In addition to rspahitz's question 2, where do the parameters barIndex and length come from?

The*almost* VBA code is as follows.

Hope this helps!

I believe the array is used to plot the activity of the symbol.

In addition to rspahitz's question 2, where do the parameters barIndex and length come from?

The

```
Sub TradeVolumeIndex(symbolIndex as String, MTV as Double, ByVal ResultArea as String)
Dim _symbolIndex as Integer 'Use for the underlying symbol index on which to calculate the indicator script.
'''
Dim _MTV as Double 'Use for the minimum tick value.
'
Dim volume() As Variant
Dim close() As Variant
Dim TVI As Double
Dim results() As Variant
Dim OutRange as Range
set OutRange = Worksheet("Sheet1").Range(ResultRange) 'ResultRange is the text defining the destination cell for the output
'Initialization
_symbolIndex = IndicatorIndex(symbolIndex)
_MTV = MTV
' Implementation
'Type Identifier Description
'Integer barIndex
'Integer length
'The assumption is that the functions return one-dimensional arrays.
'
' Get the volume of the symbol, indexed by bar index.
volume() = BarVolume(_symbolIndex, barIndex, length + 1)
' Get the closing prices of the symbol, indexed by bar index.
close() As Number = BarClose(_symbolIndex, barIndex, length + 1)
' Use for the latest trade volume index.
TVI As Number = IndicatorIndexValue()
' Use for the calculated indicator script values, indexed by bar index.
results(length - 1) As Number
' Calculate the indicator script values for the specified bar range.
For i As Integer = length - 1 To 0 Step -1
' Calculate the indicator script value for the current bar.
If (close(i) - close(i+1) > _MTV) Then
TVI = TVI + volume(i)
Else
TVI = TVI - volume(i)
End If
results(i) = TVI
Next
'To return results, copy the array results into the Range "ResultArea"
For i As Integer = length - 1 To 0 Step -1
OutRange.Cells(1,i+1).Value = results(i)
Next i
End Sub
```

Hope this helps!

Gents,

Just got back.

I will be going through your suggestions as soon as the markets close 16:00 EST, 21:00 GMT

In the meantime, I just want to let you know I really appreciate your help with this. I just hope I can get it to work.

Cheers

Just got back.

I will be going through your suggestions as soon as the markets close 16:00 EST, 21:00 GMT

In the meantime, I just want to let you know I really appreciate your help with this. I just hope I can get it to work.

Cheers

fomoruto/rspahitz

I went to the creator of the formula and all that I could find is that "Bar Index" and "Length" are just integers.

fomoruto, does that affect the formula?

Cheers

I went to the creator of the formula and all that I could find is that "Bar Index" and "Length" are just integers.

fomoruto, does that affect the formula?

Cheers

fomoruto,

Sorry if this is a silly question, but how do I test the formula?

Sorry if this is a silly question, but how do I test the formula?

Ahhhhhh, gents, I think I can explain what is meant by BarIndex.

From the formula above you saw:

Dim volume() As Double = BarVolume(_symbolIndex, barIndex, length + 1)

In this instance barIndex would mean volume which unfortunately I forgot to include in the sample spreadsheet.

In the sample spreadsheet I included the following columns

Symbol = IBM

OPEN = 10

CLOSE =20

MTV = 1000

However, there should have been another column with Volume = 20000

So barindex in this instant would therefore be 20000

Hope that helps.

From the formula above you saw:

Dim volume() As Double = BarVolume(_symbolIndex, barIndex, length + 1)

In this instance barIndex would mean volume which unfortunately I forgot to include in the sample spreadsheet.

In the sample spreadsheet I included the following columns

Symbol = IBM

OPEN = 10

CLOSE =20

MTV = 1000

However, there should have been another column with Volume = 20000

So barindex in this instant would therefore be 20000

Hope that helps.

CP,

Bar Index, length and a few other pieces are currently undefined. Until those are defined, you cannot guarantee a correct answer. More specifically, the answers are unlikely to be what you want.

again:

2) What are the definitions for the following?

BarVolume(_symbolIndex, barIndex, length + 1)

BarClose(_symbolIndex, barIndex, length + 1)

IndicatorIndexValue()

Also needing definition (where do you get the values? are they passed into the function?):

_symbolIndex

barIndex

length

Bar Index, length and a few other pieces are currently undefined. Until those are defined, you cannot guarantee a correct answer. More specifically, the answers are unlikely to be what you want.

again:

2) What are the definitions for the following?

BarVolume(_symbolIndex, barIndex, length + 1)

BarClose(_symbolIndex, barIndex, length + 1)

IndicatorIndexValue()

Also needing definition (where do you get the values? are they passed into the function?):

_symbolIndex

barIndex

length

Hope you guys haven't gone to bed yet....

rspahitz

BarClose(_symbolIndex, barIndex, length + 1)

This would be equal to Close = 10

Hope that helps

Cheers

BarClose(_symbolIndex, barIndex, length + 1)

This would be equal to Close = 10

Hope that helps

Cheers

rspahitz

_symbolIndex

Would be IBM

Cheers

_symbolIndex

Would be IBM

Cheers

Seems that _symbolIndex is not relevant to this calculation.

I would suggest setting up the function like this:

Function TVI(barIndex As Double, length As Integer) As Double

dim dblResult as Double

' calculation in here

TVI = dblResult

End Function

However, I'm not quite sure this is correct either since I don't know what you expect from the TVI function. Based on this: "It tracks the total volume" it looks like it might be a long integer, but a double should also work.

For test purposes, I'd try the above in VBA, adding the following inside the formula:

dblResult = barIndex

If you add the formula in excel, you should see it give a result. e.g.

E2 formula: =TVI(25,7)

should result in the number 25. This will be adjusted later if you can get this working.

I would suggest setting up the function like this:

Function TVI(barIndex As Double, length As Integer) As Double

dim dblResult as Double

' calculation in here

TVI = dblResult

End Function

However, I'm not quite sure this is correct either since I don't know what you expect from the TVI function. Based on this: "It tracks the total volume" it looks like it might be a long integer, but a double should also work.

For test purposes, I'd try the above in VBA, adding the following inside the formula:

dblResult = barIndex

If you add the formula in excel, you should see it give a result. e.g.

E2 formula: =TVI(25,7)

should result in the number 25. This will be adjusted later if you can get this working.

rspahitz

Thanks for responding.

I just have a few questions.

Thanks for responding.

I just have a few questions.

I'm a little confused (ok more than a little)

Where should I insert dblResult = barIndex in the formula?

Where should I insert dblResult = barIndex in the formula?

I still think we are loking at trends, so you need not just one line for IBM, but a set of values before the TVI calculation becomes useful.

So you need a set of Close and Volume numbers for the selected ticker symbol n order to calculate.

Is there a web service that provides these values on demand?

My understanding is that when you get these figures, the "length" indicates the number of records you want for the request.

http://www.forexrealm.com/technical-analysis/technical-indicators/trade-volume-index.html

Please investigate possible sources of these values and worl along the lines outlined.

http://www.nasdaqdod.com/Products/Catalog.aspx

http://stackoverflow.com/questions/281263/where-can-i-get-free-real-time-stock-data

All the best (and I*really* must get to bed now!)

So you need a set of Close and Volume numbers for the selected ticker symbol n order to calculate.

Is there a web service that provides these values on demand?

My understanding is that when you get these figures, the "length" indicates the number of records you want for the request.

http://www.forexrealm.com/technical-analysis/technical-indicators/trade-volume-index.html

Please investigate possible sources of these values and worl along the lines outlined.

http://www.nasdaqdod.com/Products/Catalog.aspx

http://stackoverflow.com/questions/281263/where-can-i-get-free-real-time-stock-data

All the best (and I

>Where should I insert dblResult = barIndex in the formula?

Below where it says 'Calculation in here:

Below where it says 'Calculation in here:

```
Function TVI(barIndex As Double, length As Integer) As Double
dim dblResult as Double
' calculation in here
dblResult = barIndex
TVI = dblResult
End Function
```

OK rspahitz,

Have a good night mate. Appreciate your help. I'm sure i'll be asking you a few more questions tomorrow. I will probably post more questions before I head off to bed, hopefully you'll check'em out tomorrow.

Cheers

Have a good night mate. Appreciate your help. I'm sure i'll be asking you a few more questions tomorrow. I will probably post more questions before I head off to bed, hopefully you'll check'em out tomorrow.

Cheers

rspahitz,

In answer to you previous question I have a broker called Interactive Brokers that provide the information on demand and stream it directly to my excel spreadsheet.

In answer to you previous question I have a broker called Interactive Brokers that provide the information on demand and stream it directly to my excel spreadsheet.

rspahitz,

If you're still around I'm getting the message #VALUE in the spreadsheet

If you're still around I'm getting the message #VALUE in the spreadsheet

fomoruto

How does the formula know where to get the volume, close?

For example, the code mentions

volume() = BarVolume(_symbolIndex, barIndex, length + 1)

and

close() As Number = BarClose(_symbolIndex, barIndex, length + 1)

How does it know where to get this information from...

How does the formula know where to get the volume, close?

For example, the code mentions

volume() = BarVolume(_symbolIndex, barIndex, length + 1)

and

close() As Number = BarClose(_symbolIndex, barIndex, length + 1)

How does it know where to get this information from...

> I'm getting the message #VALUE in the spreadsheet

then you probably did not add the above code into a separate VBA module. Go to the Insert menu and select Insert | Module.

then you probably did not add the above code into a separate VBA module. Go to the Insert menu and select Insert | Module.

Oh...you may also need to enable macros. For this, it depends which version of Excel you're using.

Oh, I thought you mentioned just adding it to the fomoruto's formula.

Do I need to create a macro workbook?

no macro workbook needed, but if you plan to use the function in many different workbooks you can choose that option. For now I'd suggest keeping it in the local workbook so it's more transportable.

ok, i've created a new module and pasted in:

Function TVI(barIndex As Double, length As Integer) As Double

Dim dblResult As Double

' calculation in here

dblResult = barIndex

TVI = dblResult

End Function

However, I'm still getting #VALUE

Hope you're still around..

Function TVI(barIndex As Double, length As Integer) As Double

Dim dblResult As Double

' calculation in here

dblResult = barIndex

TVI = dblResult

End Function

However, I'm still getting #VALUE

Hope you're still around..

I inserted TVI() in various places in the spreadsheet, but still keep on getting #VALUE.

you need to insert some parameters with the fiunction in Excel:

=TVI(25, 7)

If you still get #VALUE, you need to enable macros, which varies depending on your Excel version.

=TVI(25, 7)

If you still get #VALUE, you need to enable macros, which varies depending on your Excel version.

rspahitz

Just in case you're still there I thought I would just quickly upload what I've done. Maybe there is something silly that I've done.

sample2.xlsm

Just in case you're still there I thought I would just quickly upload what I've done. Maybe there is something silly that I've done.

See my instructions above your last post...if it doesn't work, tell me what version of Excel you have.

OK, I know you're off to sleep, just give me a second to try it...

OK, now I get the value 25

Where do the parameters, 25, 7, come from?

=TVI(25, 7)

=TVI(25, 7)

OK...now that this is working, those numbers represent:

barIndex , length

So go back to your sheet and figure out how to calculate the barIndex and the length and put those calculations in the like this:

=TVI( (A2+B2)/2, C2)

The above is probably not correct. You'll need to figure out where those values come from and create calculations for them.

Gotta run...I'll check in tomorrow.

barIndex , length

So go back to your sheet and figure out how to calculate the barIndex and the length and put those calculations in the like this:

=TVI( (A2+B2)/2, C2)

The above is probably not correct. You'll need to figure out where those values come from and create calculations for them.

Gotta run...I'll check in tomorrow.

OK, should fomoruto's formula call the function and then produce an output?

Chaps,

I know you're both fast asleep now, however I have figured it out that I don't need barindex or length. They're only used with a program that needs to chart the results. However, the formula will need the following from fomoruto's formula.

For i As Integer = length - 1 To 0 Step -1

' Calculate the indicator script value for the current bar.

If (close(i) - close(i+1) > _MTV) Then

TVI = TVI + volume(i)

Else

TVI = TVI - volume(i)

End If

results(i) = TVI

Next

'To return results, copy the array results into the Range "ResultArea"

For i As Integer = length - 1 To 0 Step -1

OutRange.Cells(1, i + 1).Value = results(i)

Next i

I still don't understand how the formula knows where to get, CLOSE, VOLUME etc. Also where is the '"ResultArea"?

Speak in the morning.... I've got to sleep.

I know you're both fast asleep now, however I have figured it out that I don't need barindex or length. They're only used with a program that needs to chart the results. However, the formula will need the following from fomoruto's formula.

For i As Integer = length - 1 To 0 Step -1

' Calculate the indicator script value for the current bar.

If (close(i) - close(i+1) > _MTV) Then

TVI = TVI + volume(i)

Else

TVI = TVI - volume(i)

End If

results(i) = TVI

Next

'To return results, copy the array results into the Range "ResultArea"

For i As Integer = length - 1 To 0 Step -1

OutRange.Cells(1, i + 1).Value = results(i)

Next i

I still don't understand how the formula knows where to get, CLOSE, VOLUME etc. Also where is the '"ResultArea"?

Speak in the morning.... I've got to sleep.

Hello I got in touch with the original developer of the formula and this is what he said about barindex:

The barIndex is the relative bar from which to calculate the indicator values going backwards a length number of bars, such that barIndex 0 is for the latest bar, barIndex 1 is for the bar before the latest one, barIndex N is for the Nth bar before the latest one.

The platform automatically calls the indicator's OnValues function and specifies it the barIndex and length for which the indicator should calculate its values for.

For example:

barIndex = 0 and length = 5 means that the indicator should calculate and return the last 5 indicator values, starting from the last data bar and going backwards 5 bars.

barIndex = 1 and length = 5 means that the indicator should calculate and return 5 past values starting from the bar that came before the last bar and going backwards 5 bars.

Therefore, it shouldn't have any impact on the spreadsheet.

Hope this helps you to help me...

Cheers

The barIndex is the relative bar from which to calculate the indicator values going backwards a length number of bars, such that barIndex 0 is for the latest bar, barIndex 1 is for the bar before the latest one, barIndex N is for the Nth bar before the latest one.

The platform automatically calls the indicator's OnValues function and specifies it the barIndex and length for which the indicator should calculate its values for.

For example:

barIndex = 0 and length = 5 means that the indicator should calculate and return the last 5 indicator values, starting from the last data bar and going backwards 5 bars.

barIndex = 1 and length = 5 means that the indicator should calculate and return 5 past values starting from the bar that came before the last bar and going backwards 5 bars.

Therefore, it shouldn't have any impact on the spreadsheet.

Hope this helps you to help me...

Cheers

...then I think we're almost there.

This is currently undefined:

Change = Price minus the extreme price since direction changed

And this is what your spreadsheet has:

SYMBOL - OPEN - CLOSE - MTV - volume - TVI

IBM - 10 - 20 - 1000 - 2000 - (TVI, TBD)

Which one is Price and which one is Extreme price, or how are they calculated?

This is currently undefined:

Change = Price minus the extreme price since direction changed

And this is what your spreadsheet has:

SYMBOL - OPEN - CLOSE - MTV - volume - TVI

IBM - 10 - 20 - 1000 - 2000 - (TVI, TBD)

Which one is Price and which one is Extreme price, or how are they calculated?

SYMBOL - OPEN - CLOSE -VOLUME -CURRENT PRICE - LAST PRICE - MTV

IBM -10 - 20 - 500 - 15 12 99

So Price would be the current price - 15 and the Extreme price would be the last price - 12

Cheers

I understand, that isn't what the sample spreadsheet looks like, however it should...

OK, then let;s change the function to the following:

You would use it something like this:

=ITV(G2, E2-F2)

After this, we can fix the function to perform the correct calculation.

```
Function TVI(MTV As Double, Change As Double) As Double
dim dblResult as Double
' calculation in here
dblResult = MTV
TVI = dblResult
End Function
```

You would use it something like this:

=ITV(G2, E2-F2)

After this, we can fix the function to perform the correct calculation.

Sorry...use it spelled correctly:

=TVI(G2, E2-F2)

=TVI(G2, E2-F2)

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

Thanks rspahitz, going to check it out now.

Rspahitz,

Please check out the attached spreadsheet. It may be far easier if you could let me know if I'm on the right track by checking out what I've done.

Cheers

sample3.xlsm

Please check out the attached spreadsheet. It may be far easier if you could let me know if I'm on the right track by checking out what I've done.

Cheers

Yup! It looks like it matches your request.

(However, I'm not sure it gives the right results, so that will have to be another discussion...getting the TVI function to perform the desired calculation.)

(However, I'm not sure it gives the right results, so that will have to be another discussion...getting the TVI function to perform the desired calculation.)

rspahitz

I appreciate it mate...

I appreciate it mate...

OK, rspahitz. There is one thing, though. The result just duplicates whatever I insert in cell D2....

Sorry...line 21 should be negative rather than positive.

...

ElseIf Change < MTV Then

Direction = Distribute

...

ElseIf Direction = Distribute Then

dblResult = PreviousTVI - Volume

...

SYMBOL OPEN CLOSE volume current price last price mtv

IBM 31.46 31.96 74 85 78 85 -74

Change is 85-78 = 6

6<74 so use distribute

result is previous ITV (0) - volume (74) resulting in -74

...

ElseIf Change < MTV Then

Direction = Distribute

...

ElseIf Direction = Distribute Then

dblResult = PreviousTVI - Volume

...

SYMBOL OPEN CLOSE volume current price last price mtv

IBM 31.46 31.96 74 85 78 85 -74

Change is 85-78 = 6

6<74 so use distribute

result is previous ITV (0) - volume (74) resulting in -74

Thanks rspahitz,

Going to check it out...

Going to check it out...

rspahitz,

Thanks for your efforts. Hopefully, another Expert will be able to pick it up where you left off....

Cheers mate.

Thanks for your efforts. Hopefully, another Expert will be able to pick it up where you left off....

Cheers mate.

Well, as far as I can tell, the problem now is not the coding but the definition.

The code matches the definition you gave.

If you change the definition to do what you want, we can fix the coding to match the new definition.

For example, try your definition by hand and see what comes out. with the example you gave me, you should get the same result I did (except that there was no previous TVI so I assumed you should use 0 to start, which might be a bad assumption.)

The code matches the definition you gave.

If you change the definition to do what you want, we can fix the coding to match the new definition.

For example, try your definition by hand and see what comes out. with the example you gave me, you should get the same result I did (except that there was no previous TVI so I assumed you should use 0 to start, which might be a bad assumption.)

Rspahitz

It was a correct assumption, 0 is the right number for the TVI

I'm not sure I could define it any better than have done above.

It was a correct assumption, 0 is the right number for the TVI

I'm not sure I could define it any better than have done above.

Rspathitz,

I was just wondering if you or another expert checked the following links I submitted above?

The first link shows the script in full. The script is a subset of vb.net

The second link describes the similarities between the script and vb.net.

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

http://www.iqbroker.com/platform/iqlanguage.aspx

I was just wondering if you or another expert checked the following links I submitted above?

The first link shows the script in full. The script is a subset of vb.net

The second link describes the similarities between the script and vb.net.

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

http://www.iqbroker.com/platform/iqlanguage.aspx

I'll check a bit later and see if I can offer updates. The problem is that I don't know what to expect from the results so I don't know if the answer is right when I get it.

In the sample you gave me a few posts back, what value are you expecting from the TVI function?

In the sample you gave me a few posts back, what value are you expecting from the TVI function?

Rspathitz,

Thanks for responding.

I will provide actually results shortly.

Cheers mate

Thanks for responding.

I will provide actually results shortly.

Cheers mate

Rspathitz,

Thanks for sticking with me with this. Here are actual values and result of a TVI

current price = 12452

last price = 12456

volume = 1240

close = 12452

open = 12458

high = 12459

TVI = 8906

Cheers

Thanks for sticking with me with this. Here are actual values and result of a TVI

current price = 12452

last price = 12456

volume = 1240

close = 12452

open = 12458

high = 12459

TVI = 8906

Cheers

1) you gave me the High, which is not in any of the equations

2) You didn't give me the MTV

3) According to the web link you gave me above:

Accumulation: TVI = TVI + Today's Volume

Distribution: TVI = TVI - Today's Volume

And for a single iteration, the TVI will always be the Volume (positive or negative)

Personally, I think the websites where you're getting the information are not right, but I'm not an expert on this.

Let me know when you get a good formula.

2) You didn't give me the MTV

3) According to the web link you gave me above:

Accumulation: TVI = TVI + Today's Volume

Distribution: TVI = TVI - Today's Volume

And for a single iteration, the TVI will always be the Volume (positive or negative)

Personally, I think the websites where you're getting the information are not right, but I'm not an expert on this.

Let me know when you get a good formula.

Thanks. You put me on the right path to resolving similar issues. Thanks dude

If you put the VB code into a module in Excel's VBA/macro area, your job may be done.

you'll then have a user-defined function that can be used in a formula in Excel.

Other than that, it's a bit difficult to filter the information in your message to determine what you want in the function.