Free EOD S&P 500 index data for export to excel - Where?

Discussion in 'Trading Software' started by Laissez Faire, Feb 2, 2018.

  1. Thanks. I will take a look just now.

    Is that through Kinetick?

    I have done this earlier with futures data, but that's in *.txt format, so I would probably once again have to convert that data in Excel.

    Seems to be a popular choice and I did try it prior to this thread, but I'm only able to watch charts, there's no possibility for downloading data. Not sure if this is a regional setting or so, since it seems like there's a "Download data" option on some of the guides I saw online...

    The problem is that all the data is contained in one column. So, I'd have to first have to convert my data from text to column.

    Next, I'd have to seek and replace all "." with ",".

    It's a cumbersome process I'd like to avoid.

    Maybe some of my Excel friends can create a VBA script that does it all in one go.
    • S&P.png
      File size:
      60.8 KB
    #11     Feb 3, 2018
  2. schweiz


    You can do it yorself, it is very simple.

    First download the data. It will be in CSV format.
    Open the file and you will see all data in the first column.
    Select the first column and find the instruction: data/ text to column (depends of your language)
    Choose SEPARATED and select KOMMA as separator.
    Choose NEXT
    Choose for the first column with the date the format YEAR MONTH DAY
    Select all the data from columns OPEN HIGH LOW CLOSE and divide the dataz by 1 000 000 (1 million)
    Put 2 decimals and you have the following screen: MWSnap068.jpg

    If you need help, let me know.
    #12     Feb 3, 2018
  3. Hi,

    Thanks! This is what I've done in the past. Then I finalize the operation using "Seek and replace" with "." to ",".

    Is this step also done in the "text to column" function...?

    I wasn't able to complete this step.

    Mind you, I need to get rid of all "." in my data.

    For the close yesterday, I'd need to have 2 762,13.
    #13     Feb 3, 2018
  4. schweiz


    Change in properties the format to NO SEPERATOR FOR 1000

    It will look then like 2762,13.
    2 762,13 is impossible I think, or will not be recognized as a number anymore.
    #14     Feb 3, 2018
  5. In settings?

    I just converted the data right now and it's fine, but it's a cumbersome process involving several steps that I'd like to avoid when updating this on a daily basis.

    Unless I don't get what you're saying and there's a simpler solution where everything is done in the "text to column" function...
    #15     Feb 3, 2018
  6. schweiz


    that means add 1 line a day???
    #16     Feb 3, 2018
    Laissez Faire likes this.
  7. Well, you're right in that regard, of course. :)

    I had a friend help me create a VBA script that does this for me now. So, problem solved, I guess.

    Thanks for all help.
    #17     Feb 4, 2018
    schweiz likes this.
  8. jharmon


    Schweiz/Laissez Faire

    Your data is terrible.

    Data for 9 Jan 2018. Zero volume??!!! That is a joke.

    Volumes on all other days - don't actually match the volumes of the constituent stocks of the S&P 500.

    Is this the sort of rubbish data Yahoo puts out these days? (is it still supplied by CSI Data?)

    Garbage in, garbage out.

    #18     Feb 5, 2018
    Laissez Faire likes this.
  9. schweiz


    It is not my data. I have a professional feed.
    I took yahoo finance just to demonstrate how to manipulate it to the wanted format.
    I would never tracde on it, and as a daytrader that feed would be useless. I use realtime datafeed.
    #19     Feb 5, 2018
    Laissez Faire likes this.
  10. Yes, I noticed that on volume. To be honest, I wasn't aware volume was available for the actual index.

    I'm only interested in the actual price anyway. Any idea if that's rubbish also?

    This is free data from Yahoo and not paid for, so no refund.

    I have a live feed through IQ Feed which I also use for historical analysis, but I don't have access to index data. I'm just looking to supplement some of my futures data with data from the actual index.

    I was hoping to get it for free as I'm not paying for that in my current package...
    #20     Feb 5, 2018