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
Last Comment
cpatte7372
ASKER
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?
ASKER
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
ASKER
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.
ASKER
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.
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.
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
ASKER
rspahitz:
I'm getting the following syntax error:
Function TVI()
I'm getting the following syntax error:
Function TVI()
ASKER
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 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 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!
ASKER
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
ASKER
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
ASKER
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?
ASKER
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
ASKER
Hope you guys haven't gone to bed yet....
ASKER
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
ASKER
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.
ASKER
rspahitz
Thanks for responding.
I just have a few questions.
Thanks for responding.
I just have a few questions.
ASKER
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 really must get to bed now!)
>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
ASKER
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
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.
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.
ASKER
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
ASKER
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.
ASKER
Oh, I thought you mentioned just adding it to the fomoruto's formula.
ASKER
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.
ASKER
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..
ASKER
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.
ASKER
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.
sample2.xlsm
See my instructions above your last post...if it doesn't work, tell me what version of Excel you have.
ASKER
OK, I know you're off to sleep, just give me a second to try it...
ASKER
OK, now I get the value 25
ASKER
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.
ASKER
OK, should fomoruto's formula call the function and then produce an output?
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.
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.
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.
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?
ASKER
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
ASKER
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)
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
ASKER
Thanks rspahitz, going to check it out now.
ASKER
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
sample3.xlsm
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.)
ASKER
rspahitz
I appreciate it mate...
I appreciate it mate...
ASKER
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
ASKER
Thanks rspahitz,
Going to check it out...
Going to check it out...
ASKER
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.)
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.
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.
ASKER
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?
ASKER
Rspathitz,
Thanks for responding.
I will provide actually results shortly.
Cheers mate
Thanks for responding.
I will provide actually results shortly.
Cheers mate
ASKER
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.
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.