Excel and VB

[dick_dastardly]

Are there any Excel geeks out there that know how to use the Visual Basic feature that comes with Excel.

I have an average understanding of Excel ( enough to do some simple calculations and create charts ) and I have an average understanding of visual basic ( enough to input/output to and from text files and do some number crunching ).

What I don't know how to do is how to transfer data between the two. ( in other words how to get excel and VB to talk to each other ).

Basically, what I'm trying to do is input a single column of price data from a spread sheet in to an array in visual basic( I'm assuming this can be done ), then, after some number crunching in visual basic, populate another column on the spread sheet with the resultant data series which I can then use to create a chart.

Thanx in advance

dd

 

[TheBramble]

dd - Excel has a rich set of number crunching and stats functions that you don't need to go into VBA for. I've had to resort to VBA for event management functions, but pretty much everything I've ever needed to do calc-wise, Excel has something for it.

Can't help you with the VBA if that's the route you take, but might be able to point you in the right direction if it's possible within Excel itself.

 

[Rhody Trader]

Basically, what I'm trying to do is input a single column of price data from a spread sheet in to an array in visual basic( I'm assuming this can be done ), then, after some number crunching in visual basic, populate another column on the spread sheet with the resultant data series which I can then use to create a chart.

Excel geek here. :cheesy:

Bramble is right that chances are most calculations can get handled in the Excel formulas, but there are definitely times when using a VBA macro makes a heck of a lot of sense. You want to put and take data from the spreadsheet. Here's a snippet of code that would place a series of numbers in a column:

For x = 1 to 10
cells(x,1).value=x
next x

That little function would place 1 in cell A1, 2 in A2, 3 in A3, etc.

Note that I've used the cells() object. The syntax is cells(x,y) where x is the row and y is the column (numerically, not alpha). The .value is the property you're working with to either get or set the contents of the cell in question. If you are working with more than just the currently active sheet you would want to use sheets("sheetname").cells(x,y).value, replacing sheetname with the name of the sheet you want to access.

You can set a variable equal to a cell value simply by using:

var = cells(x,y).value

So if you interate the data you want to pull, you can put it in to an array (if even necessary), do your calculations, then interate the data back out in to a column for plotting.

Does that help?

 

[swandro]

You can transfer a range into VBA with a single statement. And back again. Here is an example.

Dim x as variant
dim r as long

x = range("A1:A200").value

Now do your calc on the array e.g

for r = 1 to ubound(x,1)

x(r,1) ........

next

Then transfer the results to another range

range("B1:B200") = x

This is an extremely fast way of transferring data between a worksheet and a VB array. It works with multiple columns as well

 

[dick_dastardly]

Thanks to everyone who replied.

For me, spreadsheets very quickly become incomprehensible. An example would be nested IF THEN ELSE statements. I find it a lot easier to understand nested IF’s in VB because of the way you can arrange the code on the screen.

I also prefer the sequential looping nature of basic as opposed to the instantaneous ripple across effect of spreadsheets.
I don’t know why this is, perhaps its because as a kid, basic was the first language I ever learned ( on my good old Acorn Electron 32k back in the early eighties )

Anyway, I will have play around with the commands suggested above and see if I can get them to work.

Thanks again

dd

 

[dick_dastardly]

Sorted !

 

[xetrastock]

I run a spreadsheet of the FTSE 100 stocks that uses DDE to import stock data. Is there any piece of code out there that will get cells to flash briefly as they change? Is there any way to generate tick by tick charts from the data as it flows through?

So far, I can compute a value for the FTSE 100 in real time (unlike the 15 second updates the official index provider gives me), pick out the top five gainers/losers/most traded, compute total volume on FTSE 100 stocks and compute FTSE 100 indications during auction periods (based on where the uncrossing prices would be if the auction were to end then).

All help, comments welcome!

Jon

 

[A Dashing Blade]

You can transfer a range into VBA with a single statement. And back again. Here is an example.

Dim x as variant
dim r as long

x = range("A1:A200").value

Now do your calc on the array e.g

for r = 1 to ubound(x,1)

x(r,1) ........

next

Pedant alert, that really should be

for r = lbound(x,1) to ubound(x,1)

As you were.

 

[A Dashing Blade]

I run a spreadsheet of the FTSE 100 stocks that uses DDE to import stock data. Is there any piece of code out there that will get cells to flash briefly as they change? Is there any way to generate tick by tick charts from the data as it flows through?
. . .

Not with DDE as far as I know. The problem is that DDE data comes in whenever it wants ie the Excel app is unaware of a price change. What you need to do is either bring the data in through an api (get and display the data programaticaly), or use either use a 3rd party app or the new real time data fuctions in XL 2003 which should fire off the worksheet_change event on the sheet.

Assuming your price data cell is range named "LastPrice" then you can do something like this . . .

Private Sub Worksheet_Change(ByVal Target As Range)
'this code will fire off whenever the value of any cell changes on the sheet.

If intersect(me.range("LastPrice"), Target) is nothing then
. . . 'Do nothing,
Else
. . . 'You know the price has changed so process data
end if

End Sub

 

[swandro]

Not quite...

Pedant alert, that really should be

for r = lbound(x,1) to ubound(x,1)

As you were.

Well, not quite. When you transfer a range in this way, it always sets the boundaries of the variant array starting from 1. This is because the upper boundary is always equal to the number of rows being transferred.

However, as a general principle you are absolutely right.

 

[A Dashing Blade]

Well, not quite. When you transfer a range in this way, it always sets the boundaries of the variant array starting from 1. This is because the upper boundary is always equal to the number of rows being transferred.

However, as a general principle you are absolutely right.

Bonus points for extrapolating from this answer why you won't have a vba version of .net.

Extra bonus points, for giving approx row limits in doing the MyVar=Range("X") technique in XL97 and below and the hack to get around it?

 

[swandro]

Bonus points for extrapolating from this answer why you won't have a vba version of .net.

Extra bonus points, for giving approx row limits in doing the MyVar=Range("X") technique in XL97 and below and the hack to get around it?

I will have to miss out on the bonus points - I haven't used VSTO yet and I didn't use XL97!

 

[A Dashing Blade]

I understand that all collections in .net are zero based, all in XL are 1 based

It was a limit of about 1300, to get around this you kept on re-assigning a range-name to every block of 1300 cells and then . . . . f**k it, forgotten

 

[pipalot]

I run a spreadsheet of the FTSE 100 stocks that uses DDE to import stock data. Is there any piece of code out there that will get cells to flash briefly as they change? Is there any way to generate tick by tick charts from the data as it flows through?

So far, I can compute a value for the FTSE 100 in real time (unlike the 15 second updates the official index provider gives me), pick out the top five gainers/losers/most traded, compute total volume on FTSE 100 stocks and compute FTSE 100 indications during auction periods (based on where the uncrossing prices would be if the auction were to end then).

All help, comments welcome!

Jon

Hi Jon,

I noticed that a few years ago! you made a post mentioning that you were getting the FTSE stocks into Excel via DDE, including the uncrossing price.

I have been looking for a broker/data provider that would enable me to do just that (see the anticipated uncrossing price and uncrossing volume figures fluctuating realtime in Excel or via an API, during the pre-open auction). Can you please tell me which provider you got this data from, thanks?

I may be able to answer any questions you still have about Excel because I am an programmer.

Kind regards,
Pipalot

 

[NewThought]

You can transfer a range into VBA with a single statement. And back again. Here is an example...

IMO, there's not much point in doing that: the data in the spreadsheet is "in memory" anyway - so referring to it directly using the cells() method will not take significantly longer than referring to it in an array (assuming the program does not contain a loop that will need to refer to each cell thousands of times).

My basic plan is to download data for the previous 3 months (my personal preference for short term forecasting) and use this to predict what the price will be a week or so from now. I know a wide variety of languages, and C# is my favourite, but I am coming around to the idea of using Excel and VBA - it's all set up for manipulating numbers, and the development environment is very easy to use. There will be lots of examples on the web of how to download price/volume data, there will be minimal messing around to do straightforward stuff (the bane of languages like java and C# IMO), and Excel has a lot of very powerful functionality built in.

Would anyone care to comment about Excel/VBA vs other development platforms for data analysis, please?

 

[andy213]

I think I've distilled the reason why I needed to learn VBA over the common features of excel
- static storage of variables.

Yes there's a hell of a lot of overlap- but VBA can REMEMBER - excel can't - if you think it can, change a paramater leading into the formula and behold the formula also changes. It can't snap remember prices hit for exampe then change a response foreverafter -

Interestingly it IS possible (but not practical ) in excel if you go back to the classical flip flop "LATCH" circuit (wiki it then use excel logic true() false() not() functions etc) and turn auto calculation off - I went to these lengths in the past before I realised VB was the answer above applying classical logic gates to a spreadsheet using booleans

I found for trading related programs and for recording price type applications you need excel to remember a price then set a flag "it got here" not change the "it got here" flag once the price drops again to "it never got here" when it actually did and then dropped-excel is fickle like this, and this is where VBA wipes the floor with Excel.