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

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

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

So, your saying that:

=D12&" Nov10 "&Q12&" P"which is in the

Formula Bar,

get replaced with:

ED Nov10 40 Pwhich 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

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

Perhaps that is the way it is supposed to function,

doing aPaste Special, Value

as opposed to simply displaying the result of the formula.Did you get any type of documentation with the software?

MIKE

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 cellWhat 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

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 directoryTo 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.

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

ThanksAndrew

Unfortunately, the formula in cell P12=D12&" Nov10 "&Q12&" P"

gets overwritten with the value

ED Nov10 40 PSo, you input the value

EDinto cell D12

and you input the value40into cell Q12The 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

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

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

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

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

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.

Hi Mike Protecting the cells seems to have done the trick.

Thanks for the advice.

Andrew

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History