Excel 2000 external data linking and refresh rate problems 2

[brit54]

Good day,
I have created a spreadsheet that has cells linked to a financial services provider of real time stock quotes. These links utilize URL formulae such as:

http://127.0.0.1:16239/Req?getLastQuote(stock

symbol)
This command line I enter into the menu item Data\Get external data\New web query.
It returns a data streamline containing salient information on the selected stock all into a specific cell.
Then I again access the menu item Data\Text to columns to undeliminate the data stream into another designated group of cells.
Finally, I selected the cell containing the deliminated data stream, and select the External Data toolbar Refresh control (2nd icon from the left), and select a refresh rate at the fastest available which is 1 minute intervals.
With the underlying stock quote provider program running, this then updates the deliminated data stream cell data every minute, BUT IN TURN DOES NOT UPDATE THE UNDELIMINATED DATA IN THE ROW OF CELLS CREATED IN THE TEXT TO COLUMNS UNDELIMINATION PROCESS.

This is my dilemma. How do I get Excel to automatically update the final data line ?
Additionally, I would prefer to automatically update more frequently than 1 minute. Is that possible and if so how ? Ideally I would have a continuously and constantly updating stock grid of real time data.

Thanks in advance for any suggestions.
PS: I'm using W98 2nd edition on a PIII and WXP on a PIV machine.

 

[Dreamboat]

Data-Text to columns creates separated, unlinked text.

If you want to delimit the data, you'll have to do it with formulas, which should auto-update every time a change occurs in the file.

What does this "specific cell" contain after you've queried? You should likely be using VBA to query, rather than a manual process...

Anne Troy
Way cool stuff:

http://www.cafeshops.com/OfficeExperts

 

[brit54]

Anne here is an example of a 'just updated' deliminated cell:

+XBAAM,09/12/2003,21:00:00,0.95,0.95,1.05,0.00,-,320,0.95,0.85,0,0,0,0,0,0.00,0.00,0.00,

The first character in this case is an equity option, followed by date, time, last price, bid,ask,hi,lo etc etc.

 

[Dreamboat]

+XBAAM,09/12/2003,21:00:00,0.95,0.95,1.05,0.00,-,320,0.95,0.85,0,0,0,0,0,0.00,0.00,0.00,

Oops. Got it. See why you don't want to post twice? I thought I did not reply in this one last time, hee hee.

Suppose headings in Row 1 and string in A2.

Equity Option
In B2:
=left(a2,6) that is, if it's always 6 characters

In C2:
=date(mid(a2,8,10)) I could have this one wrong as far as "date", we'll see


see if you can work out the rest using left, mid, and right.



Anne Troy
Way cool stuff:

http://www.cafeshops.com/OfficeExperts

 

[brit54]

ANNE - FORGIVE ME BUT MUST LEAVE FOR 2 HOURS IMMEDIATELY - I WILL RETURN THEN - MANY MANY TX

 

[Dreamboat]

No problem.
I'm doing some "paid" work today myself, so hope you'll forgive if I'm not right back with answers... You may want to just go to that other post and "report it" and ask them to delete it, or post a link from that one to this one. We can make sure others don't go trying to help in THAT one now...

Anne Troy
Way cool stuff:

http://www.cafeshops.com/OfficeExperts

 

[Bowers74]

Just a starter,

=LEFT($A$1,FIND(",",$A$1)-1) will give you the first part of the string, no matter how long it is.

I have been working diligently on the rest of the string using the MID() function and I will "report" back with my solution as far as I can get it.

Anne, how about getting me one of them there "paid" jobs! ;-). Do you work at home?


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Dreamboat]

Mikey! Email me and I'll tell you 'bout something new that might interest you. anne@piersontech.com

Anne Troy
Way cool stuff:

http://www.cafeshops.com/OfficeExperts

 

[Bowers74]

Anne,

RE: Your Date "problem"

thread68-653167



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Dreamboat]

Who said I have a problem with Dates? I have no problem... oh. I said that. LOL LOL

Anne Troy
Way cool stuff:

http://www.cafeshops.com/OfficeExperts

 

[Bowers74]

OK,

This is how far I am:

Let's assume that your text string is in A1:

EBAY,09/12/2003,21:00:00,52.55,29.28,75.90,-0.07,D,7349957,52.65,51.00,1,1,3,0,0,51.77,25.11,58.93,

Enter =LEFT($A$1,FIND(",",$A$1)-1) into B1

Then . . . This is where I'm still having problems

In C1 enter =MID($A$1,(FIND(B$1&",",$A$1)+LEN(B$1))+1,(FIND(",",$A$1,FIND(",",$A$1,FIND(B$1&",",$A$1,1)+1)+1))-(FIND(",",$A$1,FIND(B$1,$A$1,1)+1)+1)) and autofill it over 18 columns.

The problem is that it works up to the second ",1," in the string because the FIND() function keeps finding the first ",1," in the string.

That is the progress report so far. I don't think that I will be able to work on this any more today though. I've got company coming over. It is Saturday night!!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[brit54]

Anne - Mike - you guys are the greatest. I just got back after a training session with my Lab - we're both in training for him to be a service dog for me (I'm confined to a wheelchair with MS) - am so sorry for my extended absence from this forum dialogue which is proving to be incredibly valuable to me.

I've been going over both of your posts. Just to update you both, the lady (her name is Tana) who is a helper for me with my dog training is a computer person at a major company here in Tucson Az, and she is now also engaged in this project to help me get this spreadsheet working ))

She will be joining me tomorrow morning around 0800 AZ time to dive into the project with me again then. I wonder if either of you will be available at the same time ?

[If I were able to figure out how to make my Microsoft Netmeeting to work over my Cable modem and LAN, I'd be able to interact with the file with you guys even better - I've never been able to use Netmeeting for the past 2/3 years since being on a cable modem and LAN

In the meantime, I'm going to ask the tech support folk at the data source provider (Quote Tracker - who are there 24/7 and very quick to respond every time) if it is possible to somehow breakup the data stream so as to bring in just one piece of data from that string. IF so, that could make all of our lives a heck of a lot simpler don't you agree ?

I'll report back my findings.

BTW, where are you guys located ? It sure would be good to know so I don't go pestering you at weird hours your time ) Also tell me please the best times to communicate with you both. Thanks.

Peter E.
Tucson, AZ

 

[Bowers74]

Hi Peter,

Thanks for the

, even though I haven't really earned it yet.

I have been trying to get my a.m. formula to work, but it keeps crapping out whenever it reaches a duplicate value in the text string. I am almost positive that you will have to use VBA programming to get this to run like you want.

Have you tried creating a Web Query (Data->Get External Data->New Web Query) to get your data? I tried, using the URL that you posted above, but the link is either secure or non-existant, so it didn't work. If you can connect to the site you are querying, then you shouldn't have a problem running a Web Query though.

By the way, I live in Germany (AZ time + 8 hours) that means at 0800 in AZ it is 1600 here. That is around the time that I get home from work (1700), so I should be able to communicate with you anytime (unless I had a bad day at work, then I don't go on the PC) ;-).

I hope you get this problem solved.

P.S. - If you ever get NetMeeting to work over a Cable Modem, I would love to know how, because I am using a DSL modem and I would love to try it out w/ my family in FL (I am an American, I just live in Germany ;-)).



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Dreamboat]

Wow. That's a lot! It's 7:38 am here (about an hour from Philadelphia) or AZ + 2 or 3 (not sure). I can be reached on Yahoo instant messenger, which has voice capabilities. My moniker there is thewordexpert. Since I got Windows XP, I've not managed to communicate using Netmeeting. I used to support it and troubleshoot it too. But in XP, all I get is something about the person I'm trying to reach must be behind a firewall. I hate sharing my desktop anyway. Everybody sees my mistakes.

Excel text formulas are the worst for me. However, I did once compile a Text Tools download, available here:

http://www.theofficeexperts.com/downloads.htm#ExcelDownloads

I'm sure I had help, and cannot remember at all what's in that file, but it could be helpful.

Anne Troy

 

[Dreamboat]

Ok. The link didn't work. Here it is:

http://www.theofficeexperts.com/downloads.htm#ExcelDownloads

There's also a download called XLFunc. It's Excel's help file function reference in a cool format. I hate help file format.

Scroll below Dave's annoying ads at:

http://www.ozgrid.com/Excel/TextFormulas.htm

This was easy to understand:

http://spectrum.troyst.edu/~techtip/activities/teachexcel/special/text.htm

Anyway. I'm around!

Anne Troy

 

[fredericofonseca]

Peter,

Regarding the data you are retrieving, does it always have the same number of fields or is it varying? (even if empty the field should be counted as valid).

The way you are trying is valid, but leads to a HUGE "IF" and that is hardly maintainable, so if the above is true there is another way to do it, apart from the other sugestions already made

(and my email is frederico_fonseca at the obvious domain.)


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[brit54]

Thank you all so much for your continued efforts on my behalf - I just don't know what to say, I'm so grateful.

Anne (ladies 1st guys!):

Phily is currently 3 hours dif from AZ until the clocks fall back - then it becomes 2 hours 'cause AZ doesn't move its clock.

BTW, until moving out here in 1994, we had lived in Pittsburgh for 12 years. I'm originally from England and have been in the US of A since 1968 or thereabouts - mostly in the NY area till PA in 1982!

Anne, we'll have to try Yahoo's IM - it will be great to hear your voice too

Sounds like Frederico may be able to help us all get our Netmeeting to work for us. I'll get into that project myself after this issue is finally put to bed. Anne, I too used to get similar error messages with Netmeeting with people behind firewalls etc., and I'm using W98SE (I've got a new PC not yet hooked up with XP as the OS - that too will wait till this problem is resolved).

I will wait till Tana arrives before delving into thelinks you provided Anne. She'll be here in the next hour or so.

Mike: whereabouts in Germany? For 4 years in the early sixties we lived in eastern France not far from Strasborg (Sp ?), and used to nip into Germany all the time - Heidelburg, Baden Baden, Frankfurt, Weisbaden etc etc. Last there in 1992 on an extended trip to re-visit our old haunts.

Yes Mike, I have and am using the web queries you mentioned which link to the outside URL data source at Quote Tracker (a free stock tracking program that you download and install). Then with that program running in the background, my web query connect to it to retrieve the real time stock data - all into a single cell (per stock or option item) which updates itself automatically every minute - the shortest available - (through the tools provided in the external data toolbar).

Mike, see Frederico's post above regarding Netmeeting. I bet he can get us up and running finally ! I plan to pursue that line of inquiry after putting this issue to bed.

Frederico: Ola ! Hablas Espanol ? - yo vive en Ecuador por dos anos - 1972 - 73, pero hoy dia mi espanol es terrible porque no tengo la opportunidad por practicar ! Mi esposa es una ecuatoriana, pero nosotros no hablamos espanol en la casa. Porque no sais, pero es verdad. A wasted opportunity

I look forward to working with you to get my Netmeeting up and running after all this time. I just have to first fix this damn spreadsheet problem which for me and my investment activities is absolutely critical.

Frederico - for the dummy amongst us, please give me the 'obvious domain' so I can put you with Anne in my address book. Thanks.

Peter
Tucson AZ

 

[Dreamboat]

Peter:

1. You're right. Your spanish is as bad as mine, but at least I don't try to write it! Haha! LOL

2. Frederico's email would be frederico_fonseca (insert @ sign here) syssoft-int.com

Many people don't directly show their email address because there's softwares out there that scan the web for just this reason: to pick up text with a syntax such as text@moretext.com

The "obvious" domain would be the use of the website name that he posted. Lots of people write like:

anne(at)nospam.piersontech.com or
anne(at)piersontech.com or
nospamanne@piersontechdotcom
or just about any way imaginable.

Anne Troy

 

[brit54]

Anne - TX - I forgot to tell you wot Quote Tracker tech support told me last night. I'll post the whole email I sent them and their response:

Peter,

We do not have a limitation of max 1 per minute for the QT Server API.

As for separating data into cells, a simple macro should do it. Cannot bring just one item of data. Sorry.

If you still have a problem with the macros, you can try using this add-on for QT

http://www.engo.com/tradingtools/default.htm

Look under StocksToXML section. They basically automate much of the work for you.

BTW, saw you registered. However, you seemed to have done it twice. I assume the 2nd one was a mistake, so I voided it.


-----Original Message-----
From: peter earnshaw [mailtoeterse@cox.net]
Sent: Saturday, September 13, 2003 11:08 PM
To: support@quotetracker.com
Subject: I'm registered for a year !


You guys have really impressed me over the past week with your 24/7 support and rapid fire responses to all my emails. You really deserve your success. Thank you.

May I ask yet another question in my ongoing saga to enable my Excel spreadsheet with your data stream ? I am a long time customer of Ameritrade, so your program (recomended by one of their tech managers I might add) is proving to be the answer to my prayers for constantly updating streaming data for stocks and options. Their 'Streamer' cannot be linked off site to my spreadsheet - it uses Sun's Java applets.

I have successfully incorporate your linked instructions to my Excel sheet so am able to get the data downloaded at the maximum rate permitted by that program of evberyy minute. (

http://127.0.0.1:16239/Req?getLastQuote(symbol)

I have successfully deliminated the data in that cell to a destination row of cells of my choice.

The step 1 process works like a charm, but unfortunately the updatng process does not carry through to the deliminated data; I have to manually redo the cell delimination in order to get the latest data in the final cells.

I'm working hard with all kinds of experts to try and find a solution all day today and tomorrow to get this sheet up and running in advance of the market open on MMonday. We're looking at Visual Basic, formulas, macros, the works - its getting somewhat confusing

One thought that my helpers suggested was that I ask you if it is possible to bring in just one particular item of data from the data string - if so that could make life a whole lot easier For example, the last price. Bid and Ask - as is all the data in the string - are great too, but to get this thing so the deliminated cells update is a real challenge !!

Peter E.
Tucson, AZ

 

[fredericofonseca]

Peter,

Just so it is clear why I said the "OBVIOUS DOMAIN", if someone places a link to a company website, then this domain is the one to use. (syssoft-int.com)

I do understand spanish (spoken and written), but I'm not able to speak/write it correctly. This is because Portuguese people like me don't like the spanish guys!! (Not really, but never had the need).

Currently on Ireland FYI.






Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com