Excel - get external data, delimiter prob

Discussion in 'Data Sets and Feeds' started by xs900, Mar 16, 2004.

  1. xs900


    I can import data from this site :


    the data appears but not in the correct format i.e. columns are not dividing in the right place so it may be something to do with the delimiters (comma on the web page!) seems that the data is imported as a table.

    I just want the correct data in the correctly spaced column :)

    I can import it as a text file (correct spacing) but then cannot refresh it (automatically - every minute)

    I have Excel 2000

    Any ideas out there?

  2. Funster


    I have excel refresh my stock portfolio from Yahoo in exactly the way I want it. There is a screen you havent seen yet that allows you to keep your column widths etc on refresh.

    Data - Get External Data - Data Range Properties.

    The other thing is that the first time you set up a web external source excel turns off auto calculations (if, like me, you have other stuff which looks at the refreshed data)

    Turn it back on again at

    Tools - Optiions - Calculations - Automatic
  3. xs900


    Thanks Funster

    I got the earlier problem sorted out (i.e. having the web query data arranged in columns) I found the solution in: Data - "text to columns."..............however, when it refreshed after 1 min, it put the original data into cell A1 and did not update the correct columns :confused:

    I have no excel experience and am struggling at the minute (probably straight forward for those who have tried this before)

    I enclose the file
  4. Funster


    Hi XS,

    I had a little go at this but it is not perfect.

    I have attached two macros to two different buttons for you (yes, enable macros - it is safe in this instance).

    One button does the web download and then sorts the columns.

    The other just sorts the columns (as excel already has the automated web download)

    I was trying to get it to auto column sort but excel didnt seem to like it. Perhaps someone else can update my code*.


    *(Technie Note: I tried attaching my code to the calculate event and had a cell in the worksheet make a simple calculation to trigger it. However it would run the code several times and, it appears, backwards in order?!)
  5. xs900


    That was generous of you..........and encouraging.

    I have been struggling for some time to find a method of RESTARTING a chart at a particular moment (min or second).....not provided by any charting vendor (that I could find.)

    I then would like to try looking at the data (Graph - bar chart) by combining various amounts (time) i.e. 3 min, 47 min, 27 sec. etc. and looking for a pattern that I think describes what I call: non-random price behaviour.

    My "gut" feeling is that this non-random price activity is always present.........but it appears in only a few timeframes (which change.) Sometimes in the (E.G.) 6 min chart or the 72 min chart etc. It could be tested using excel but I just don't have the temperament (or patience :) to start to learn what I need to do. One thing I have learned over this last few weeks (on various boards) is that the english language is not equipped to "clearly" explain what it is that I would like or need..........so easy to misunderstand the meaning of words etc. That sounds unfair - perhaps all languages (written) are the same........it is just that: nothing beats the "face to face" explanations.

    It has been an (extremely) frustrating exercise........as a trader (or one that trades) you have to be soooooo many things.....and I am no programmer.

    I am 36, son of a farmer and been (very) interested in trading for 15 years. Is it worth it?? (I mean: software problems, different data feeds, brokers......I am wondering!) not sure about the frustrations and poor sleep patterns etc.

    My idea is simple and am only at the start of trying to get it programmed. (but want to keep it to myself....for now) but I was over in Belfast on thursday (with a friend on his birthday) and when I came back I saw your post Funster and I nearly cried (sounds soppy! - I know).....but frustration was mega! Your generosity was a big encouragement.


  6. Funster


    Hi xs, sorry didnt mean to make u cry :)

    For some interesting pointers on how to program and use macros in excel try


    Also, for what you are doing it is probably easier (albeit expensive and initially a steep learning curve) to buy either tradestation or wealthlab to test these ideas of yours. After all this game is hard enough without trying to reinvent the wheel yourself !!

    Be lucky,

  7. xs900


    Been crying a lot recently - Ha Ha :) ............I must be one of these "new men" and didn't know.

    This board could be better value than those pyscologists (spelling?)etc. or people like Van T. etc, etc..........."just needing someone to talk to, who will understand" :D

    I'll try these avenues you've shown.

    U B Lucky 2
  8. Hi guys.

    I need to query a page in options clearing corp http:www.theocc.com. I need to retrieve options symbols such as IBM PH or IBM PV which gets translated into appropriate calls/puts,strikes,etc.

    I'd like to do it programatically wherein Excel has the stock symbol in a cell (eg. Cell A1). It then queries the web and plugs the symbol found in A1 into the dialogue box required by the web page of OCC. Any ideas ? Do I have to learn perl for this?
  9. xs900


  10. Funster


    I'll have a go for u. Like an excel challenge.

    Wait till after market hours though (naughty, naughty not trading !!!)
    #10     Apr 7, 2004