Active-X Kills Formulas in Excel

Microsoft Excel 2003 (full product)
June 4, 2010 at 10:02:57
Specs: Windows XP Pro, Athlon64 1gb
Hi

I have an issue with an Active-X module from myTrack, that enables live linking of stock and equity option prices into Excel. I'm using it with Excel 2003 and I have the same issue with Excel XP.

I have an Excel formula that determines which equity option price I want and this is picked up with a formula =mtAsk(P12) where cell P12 contains the formula with the ID of the equity option I'm interested in.

I get this weird thing where the formula in P12 gets converted to the text of the result. The formula is

=D12&" Nov10 "&Q12&" P"

and it gets converted (ie/ hardwired) to the result which is

ED Nov10 40 P

I spoke to TrackData who own this stuff and they pretty unhelpfully referred me to Microsoft.

Anyone have any ideas?

Andrew


See More: Active-X Kills Formulas in Excel

Report •


#1
June 4, 2010 at 10:19:36
I've never used or even heard of myTrack.

What does the data look like before you try and grab it
and what is it supposed to look like after you grab it.

What does D12 contain?
What does Q12 contain?

What is mtAsk() supposed to do?

MIKE

http://www.skeptic.com/


Report •

#2
June 4, 2010 at 10:30:16
Hi Mike

well, myTrack is some software that downloads stock and options prices. It's basically this broker's online trading software. So prices get picked up off their servers over the internet and download into myTrack. It comes with this Excel ActiveX module that brings data from myTrack and feeds it into Excel.

The data delivered is a price, say, 4.7 which goes in as a number as a result of the formula =mtAsk(P12) in this case, the asking price.

mtAsk is actually a VBA function. The mtAsk function works fine, it is just this odd effect on the formula in the input cell that is perplexing me - that the formula in P12 gets replaced with the formula's result as I mentioned..

I hope this helps

Andrew


Report •

#3
June 4, 2010 at 10:48:19
So, your saying that:

=D12&" Nov10 "&Q12&" P"

which is in the Formula Bar,
get replaced with:

ED Nov10 40 P

which is the results of the =myAsk() function/macro?

Did the program ever work correclty, at any time, or is this a new phenomenon?

If it's new, what have you done to your system that is now different then when it worked correctly?
Did the myTrack people update the software?
Can you post the macro or is it locked?

Just my opinion, but it sounds like the function/macro is the culprit.
Perhaps someone else may be of more assistance.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
June 4, 2010 at 11:33:27
Hi Mike

The formula =D12&" Nov10 "&Q12&" P" produces

ED Nov10 40 P

which is the string which is input into the =myAsk() function. To get the equity option price. In this case the option is, Consolidated Edision (ED) options for November 2010 strike at strike 40 and put options.

The =myAsk() function works fine, it is just that somehow the function in the input cell

=D12&" Nov10 "&Q12&" P

gets replaced with

the result

ED Nov10 40 P

very strange!!

Andrew


Report •

#5
June 4, 2010 at 11:50:19
Perhaps that is the way it is supposed to function,
doing a Paste Special, Value
as opposed to simply displaying the result of the formula.

Did you get any type of documentation with the software?


MIKE

http://www.skeptic.com/


Report •

#6
June 4, 2010 at 12:00:39
Also, please understand that I have no idea what your workbook looks like, I don't know what is does, or how it is supposed to operate.

The =myAsk() function works fine, it is just that somehow the function in the input cell

What input cell, P12?
What are you inputting to it?

If your inputting to P12, where does the
=D12&" Nov10 "&Q12&" P
formula come into play?

MIKE

http://www.skeptic.com/


Report •

#7
June 4, 2010 at 12:03:44
Hi Mike

actually, it doesn't even mention the Excel linking in the software help file.

I was given this when I first enquired about it by their online interactive help desk


Excel Link
==========
The Excel Link allows you to display dynamically updating market
data information in your Excel spreadsheet. The Excel Link is
not a DDE interface but a fully integrated Active-X module that
allows you to seamlessly incorporate myTrack market data into
your spreadsheets.

Excel Link is compatible with Excel 97 and later.

Installation
------------
- Installl and start myTrack
- Start Excel with a blank sheet
- In Excel click on tools/add-ins/browse
- Go to the directory where myTrack is installed
(default is c:\program files\mytrack) and select "mt.xla"
- Load the spreadsheet mttest.xls from the myTrack directory

To use a myTrack formula in a cell:

- In Excel click insert/function/user defined
- Select one of the functions
- Enter the reference cell containing a stock symbol
- If for any reason market data stops updating, click the
"MT-Resync" menu button.


Report •

#8
June 4, 2010 at 12:11:04
Mike

I have a cell P12 that calculates the string - the option identifier - for the datapoint that i want to link. With this function:

=D12&" Nov10 "&Q12&" P"

and produces value

ED Nov10 40 P

the formula

=mtAsk(P12)

is in cell S12, it picks up the value in P12 which is

ED Nov10 40 P

and goes off to myTrack to get the value for that item and returns it putting the value in S12. It returns the correct value. Unfortunately, the formula in cell P12

=D12&" Nov10 "&Q12&" P"

gets overwritten with the value

ED Nov10 40 P

for some reason. This matters because if the stock price were to fall, I'd be interested in the option

ED Nov10 35 P

but because the formula was overwritten I'd still be getting the value of option

ED Nov10 40 P


Thanks

Andrew


Report •

#9
June 4, 2010 at 12:35:32
Unfortunately, the formula in cell P12

=D12&" Nov10 "&Q12&" P"

gets overwritten with the value

ED Nov10 40 P

So, you input the value ED into cell D12
and you input the value 40 into cell Q12

The formula in cell P12 =D12&" Nov10 "&Q12&" P"
then resolves itself into the string: ED Nov10 40 P
Cell S12 now takes the resulting string and feeds it into the macro =myAsk(P12) which returns your correct value.
The only thing in operation is the macro.

Can you monitor this operation and see exactly when the overwrite occurs?
After inputting your values, highlight the P12 cell and see when it changes.

I would still guess that it's the macro/function that is doing it.
Are there other macros in operation on the sheet that might be getting involved unintentionally?

MIKE

http://www.skeptic.com/


Report •

#10
June 4, 2010 at 13:21:28
Hi Mike

you've got the logic on the analysis right. In fact, the spreadsheet is just an I/O system and all the analysis is done in a bunch of VBA user-defined functions. It seems to kill the formula when it feeds data in from myTrack. I have calculation on Automatic and it is when is calculating due to new data that the formula gets overwritten.

I had a thought that maybe if I protect the cells that get overwritten it might cure the problem. I'll let you know how it goes..

Andrew


Report •

#11
June 4, 2010 at 13:28:03
Contact myTrack again and tell them you've got faulty software and explain the steps that result in the error.
It's their macro, they should be able to fix it without tooooo much trouble.

MIKE

http://www.skeptic.com/


Report •

#12
June 4, 2010 at 13:43:33
Hi Mike

you would think so wouldn't you. I suppose I should feel indulged that when I asked them about this they were merely short and disinterested rather than actively abusive..

Andrew


Report •

#13
June 4, 2010 at 13:50:13
If you paid for the software,
I'd keep calling,
ask for a supervisor,
get names and keep pushing,
your not the only one who got bogus software, there are probably others out there, they get enough complaints they should respond.

Go to the website and see if you can get the CEO's name and/or email address and send him a friendly greeting explaining their lousy tech support and your need for some type of solution.

MIKE

http://www.skeptic.com/


Report •

#14
June 4, 2010 at 14:50:06
I'll see if the cell protection works first. The software is inactive now because the markets have closed so I can't really test it out. I'll let you know if it works, which I think it probably will.

Report •

#15
June 7, 2010 at 08:29:56
Hi Mike

Protecting the cells seems to have done the trick.

Thanks for the advice.

Andrew


Report •

#16
June 7, 2010 at 09:03:22
Glad you got your problem solved.

MIKE

http://www.skeptic.com/


Report •


Ask Question