Excel non recognition/calculation of data in cells

TheStudent · Jul 30, 2009

Websites often insert hidden character strings in data to prevent easy usage of their work.

If you want to get around it, you need to sit down and look at functions like LEFT(), RIGHT(), MID()

and various ways of turning text into values.

Excel has a lot of functions to mod strings, you just have to sit down and learn them in order to deal with this.

mokwit · Jul 30, 2009

dtrader98, that nailed it! Thanks for going to the trouble to create and post your explanation.

thestudent. Thanks for input. Have used Excel professionally for 15 years and never come across this. Seems we are always learning with Excel.

TheStudent · Jul 31, 2009

Excel can be quite esoteric I find.

esp with VBA - I can never remember all the referencing conventions.

It almost doesn't matter how long you have been using it because usually you've been using it all that time to perform a certain kind of function.

Excel is extremely flexible and has been used and designed to perform many functions, and most users are ignorant of the other possible functions despite lengthy experience.

Excel is very broad, but not very deep. Sometimes the best combination is to hook up specialist software (like Matlab) to an Excel front end to get the best of both worlds.

It's definitely worth buying and reading Excel books or signing up for a tutorial because there is always some useful feature in it you have never heard of.

PitchBlack · Jul 31, 2009

install the add-in ASAP Utilities.

The best add-in ever for Excel and FREE.

http://www.asap-utilities.com/

and:

http://www.datapigtechnologies.com/

dtrader98 · Jul 31, 2009

Most people would really be amazed at what can be accomplished in excel/vba.
There are very few limitations, however, community package development and 3D graphing capabilities both suck.
And that's where some limitations kick in.

They have an R/excel interface that looks pretty sweet, although I haven't played with it yet.

Quote from TheStudent:

Excel can be quite esoteric I find.

esp with VBA - I can never remember all the referencing conventions.

It almost doesn't matter how long you have been using it because usually you've been using it all that time to perform a certain kind of function.

Excel is extremely flexible and has been used and designed to perform many functions, and most users are ignorant of the other possible functions despite lengthy experience.

Excel is very broad, but not very deep. Sometimes the best combination is to hook up specialist software (like Matlab) to an Excel front end to get the best of both worlds.

It's definitely worth buying and reading Excel books or signing up for a tutorial because there is always some useful feature in it you have never heard of.

TheStudent · Aug 4, 2009

Quote from dtrader98:

Most people would really be amazed at what can be accomplished in excel/vba.
There are very few limitations, however, community package development and 3D graphing capabilities both suck.
And that's where some limitations kick in.

They have an R/excel interface that looks pretty sweet, although I haven't played with it yet.

Yes - I think what really sucks actually is the computational power. Excel is not very efficient at dealing with large datasets.

Graphing has never been a strong point with Excel, but I find it adequate.

mokwit · Aug 4, 2009

DDE's topping out at maybe 1000 fields can be a very low limit for stocks in practice. I understand RTS (RTD?) capabilities are not that much greater.

syswizard · Aug 6, 2009

Quote from mokwit:

DDE's topping out at maybe 1000 fields can be a very low limit for stocks in practice. I understand RTS (RTD?) capabilities are not that much greater.

Wow, you are watching over 200 symbols at once ?
In any case, the problem with DDE is that most implementations return market data randomly for each DDE-referenced cell. The best approach is to return the data for ALL cells at the same time into an array on a timer. Much less messaging traffic...much lower CPU usage as well.

mokwit · Aug 10, 2009

Quote from syswizard:

Wow, you are watching over 200 symbols at once ?
In any case, the problem with DDE is that most implementations return market data randomly for each DDE-referenced cell. The best approach is to return the data for ALL cells at the same time into an array on a timer. Much less messaging traffic...much lower CPU usage as well.

Hi Sys, Thanks for your input. Will look into what you suggest.

You are right regarding 200 symbols, in fact with 3 levels of bid/ask + last price + last size it would probably go down to 125. I say would probably because the number of stocks that I can watch is so small I decided to hold back on investing time writing some book monitoring algorithms I have had in my mind for quite some time. With 1000 fields translating 125 stocks it did not seem worth it, but I recently took another look at this as it would have value with a watchlist rather than a whole universe and I have found a way of working around some other obstacles and prescreening and reducing the number of stocks to watch.

What I plan to do is write it for a watch list of probable capacity limit of around 125 stocks with a view to being able to expand if Excel+ capabilities expand. It should give me a much better (earlier) heads up on watched stocks than simple price or volume changes.

As I understand it the main limiting factor for DDE is memory and I am wondering if it may be that 1000 +/- 200 or so upper limit for DDE fields in Excel arises from the fact that memory formerly topped out at 4GB for all. As 16GB is soon/now viable I am wondering of the upper limit for DDE/RTS(D?) increases.

An algorithm in Excel that will alllow Excel to watch several hundred stocks order books seems not yet possible, (at least in Excel I am sure it can be done in C# or some such). With TS2000i Radarscreen I am monitoruing 5000 stocks, but not sure if I can do in Easylanguage what I think I can do in Excel - maybe, in which case that might be my solution - storage of previous data in arrays permitting and data vendor plus vendor-> tradestation adaptor data fields capabilities permitting

syswizard · Aug 10, 2009

Quote from mokwit:

Hi Sys, Thanks for your input. Will look into what you suggest.

You are helpless on this one...it all depends upon the DDE SERVER's implementation.

As I understand it the main limiting factor for DDE is memory and I am wondering if it may be that 1000 +/- 200 or so upper limit for DDE fields in Excel arises from the fact that memory formerly topped out at 4GB for all. As 16GB is soon/now viable I am wondering of the upper limit for DDE/RTS(D?) increases.

I think you are wrong on this. The limiting factor has got to be CPU.

An algorithm in Excel that will alllow Excel to watch several hundred stocks order books seems not yet possible, (at least in Excel I am sure it can be done in C# or some such). With TS2000i Radarscreen I am monitoruing 5000 stocks, but not sure if I can do in Easylanguage what I think I can do in Excel - maybe, in which case that might be my solution - storage of previous data in arrays permitting and data vendor plus vendor-> tradestation adaptor data fields capabilities permitting

Only thru some sort of Excel DLL or XLL written in C would you be able to do this. It would have to send snapshots of each book every 250 milliseconds for instance. So you wouldn't be getting every tick. So it's not possible to match a multithreaded app like Tradestation's Radarscreen as Excel is singled-threaded for the most part.