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;
[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]; }
}
FYI
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.
' 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
rspahitz
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:
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 resultsEnd Function
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= resultsEnd Function
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.
If this works I won't be able to thank you enough. Seriously mate, thank you.
Going to check it out now.
cpatte7372
ASKER
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.
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()
Francis Omoruto
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.
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 VariantDim close() As VariantDim TVI As DoubleDim results() As VariantDim OutRange as Rangeset 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 iEnd Sub
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
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.
Where should I insert dblResult = barIndex in the formula?
Francis Omoruto
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.
>Where should I insert dblResult = barIndex in the formula?
Below where it says 'Calculation in here:
Function TVI(barIndex As Double, length As Integer) As Double dim dblResult as Double' calculation in heredblResult = barIndex TVI = dblResultEnd Function
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
cpatte7372
ASKER
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.
cpatte7372
ASKER
rspahitz,
If you're still around I'm getting the message #VALUE in the spreadsheet
Oh, I thought you mentioned just adding it to the fomoruto's formula.
cpatte7372
ASKER
Do I need to create a macro workbook?
rspahitz
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, should fomoruto's formula call the function and then produce an output?
cpatte7372
ASKER
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.
cpatte7372
ASKER
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.
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.)
Thanks for your efforts. Hopefully, another Expert will be able to pick it up where you left off....
Cheers mate.
rspahitz
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.)
cpatte7372
ASKER
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.
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?
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.
cpatte7372
ASKER
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.