Home > Tools of the Trade > Trading Software > Free EOD S&P 500 index data for export to excel - Where?

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

  1. Hi all,

    I'm interested in obtaining EOD closing prices for daily data on the S&P 500 index. Living in Europe, I have a different setup than in the US, such that any downloads from a US site seems to put all the data in one column.

    I can fix this in Excel by converting the data and changing system settings, but it's out of the question to be doing that all the time and I haven't yet found a more elegant solution.

    Any ideas or pointers? :)

    Thanks in advance.
  2. Have you tried Quandl?
  3. No. Any further advice?

    I could add that I already pay for historical and live futures data. It's simply the index data I'm now interested in adding as well and I would prefer not to pay for that one.
  4. Ah sorry. I misread your post. You want the index close, not futures. Quandl does offer that but it’s under premium offered by bar chart.com
  5. You might not have misread as the title was corrected after I posted it. :)

    I already have futures data. Would like to follow the index also. Thanks anyway.
  6. You can get free Eod data with ninjatrader. No need for a ninjatrader license either.
  7. Googlefinance
    Google it.
  8. 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.
  9. 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.
  10. 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.
  11. 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.
  12. 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...
  13. that means add 1 line a day???
  14. 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.
  15. 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.

  16. 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.
  17. 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...
  18. Its 4am atm where I am, later, I will knock up some formula examples for you to try.
  19. Free googlefinance data is not downloadable into a standard excel spreadsheet, other than converting into another format that I'm aware of, and it is not the method I use.
    This is a simple effective method....
    Either buy a chromebook and use the spreadsheets provided or...
    Simply open Chrome web browser on your PC and at the menu bar it says 'APPS'.
    Click on this and then click on 'Sheets'
    You will need a google account, but it's easy enough to subscribe. A subscription will give you access to their cloud service. I have a chromebook, when you buy one of these it provides one with 100GB of free cloud service for 2 years.
    Anyhow, on the spreadsheet from Chrome, enter your formulae and you will have free stock and indexes data which updates automatically, these from various exchanges around the world.
    Next post I will illustrate the formulas.
  20. 111.PNG 222.PNG
  21. ignore the little dot prior to the equals sign in the example above, I needed to do that in order to get it to display as a text file, the format function in this instance wasn't behaving so had to use this method.
    Yesterday date and day before date look identical, only because my computer is working off a different date than currently in USA.
  22. These are functions which will supply data results...