Hi!
I have generated the data and the charts you are looking for. Let me
first explain how I generated the data:
1) I found the historic closing prices of the indices on Yahoo!
Finance. For eg., to see the historic values of the DJIA, check out:
http://table.finance.yahoo.com/m?a=1&b=1&c=30&d=5&e=21&f=02&g=m&s=%5Edji
2) There is an option on that web page from Yahoo!, which allows you
to download the data into spreadsheet (csv) format.
3) For each of the indices you list, I downloaded this data, opened it
in Microsoft Excel, and with help from MS Excel, processed it and
generated the charts you are looking for.
Please note that I could only obtain data since the following dates:
DJIA: since Jan 1, 1930
S&P 500: since Jan 1, 1950 (S&P 500 was introduced in 1957. The data
before that is from its predecessor).
S&P 100: since Sep 1, 1982 (S&P 100 was introduced in 1983. The data
before that is from its predecessor).
You can get the files containing a total of 12 charts for the 3
indices, along with all the data and formulas used to generate them
at:
http://us.f1.yahoofs.com/users/935698e7/bc/Illusions/StockCharts.zip?bcEBs68AbbiPEcRl
Please let me know if you have any questions about the charts or if
you need help with processing the data further...
Thanks,
Infoscout-ga. |
Request for Answer Clarification by
recoveringtypea-ga
on
21 May 2002 18:14 PDT
Hi:
The link you gave me for the answer didn't work [I tried it many
times]. Any clues what went wrong. If for some reason we can't get it
to work, maybe you can fax me the information/charts.
Last, I have Excel, but don't know how to use it--is the operation you
did easy for a neophyte to figure out?
thanks,
Marshall
|
Clarification of Answer by
infoscout-ga
on
21 May 2002 19:20 PDT
Hi Marshall,
I'm sorry for the trouble. The link I gave doesn't seem to work
anymore! The data is really huge (the charts and the data will print
over 50 pages), so I wanted to give you the excel file. Thats where I
stumbled, as google doesn't support attachments and the server I
uploaded it to is not working the way I expected.
Anyways, I can fax you the 12 charts (without the detail data). Please
tell me the number.
Thanks,
Infoscout-ga
|
Clarification of Answer by
infoscout-ga
on
21 May 2002 21:22 PDT
Hi Marshall,
I have found another way to get the excel files to you. Can you please
go to:
http://groups.yahoo.com/group/infoscout-ga/files/
Under there, I have a folder for you (called "recoveringtypea-ga"),
which contains 3 excel files.
I prefer giving you the excel files, as they will provide you more
information on the individual data points, which I think will be more
useful. If there are any issues with accessing the files, or with
excel, please let me know and I will be happy to fax over the graphs
to you.
Thanks,
Infoscout-ga.
|
Request for Answer Clarification by
recoveringtypea-ga
on
22 May 2002 07:22 PDT
Dear infoscout:
I was able to access the data. You’ve done a great job and given me
almost exactly what I was looking for with the following
caveats/requests in order of importance:
1. I asked for a break down of the # of months in each category [ie.
how many 0-2+% months, bimonthly, trimonthly etc periods were there;
how many -0-2% periods, 2-4, 4-6, 6-8, 8-10, 10+ [in both positive and
negatives]. While it would be nice to have these totals for S&P500
too, if this is a tricky thing to do, then doing it just for the DJIA
is fine [I’m hoping/assuming this shouldn’t be that hard to do].
2. For some reason the chart for DJIA Quarterly Returns is for Present
-Jan 1950, instead of 1930. Is that a typo? If not, could I have the
present to 1930 version?
3. I have Microsoft Excel 97. Is it possible for you to e-mail me the
data you got so I could save them as files and possibly use them
again? My e-mail address is marshall@surfglobal.net?
Again, thanks for your efforts; we’re very close here and as I said
you’ve done a great job and I really appreciate your work!
Best,
Marshall
|
Request for Answer Clarification by
recoveringtypea-ga
on
22 May 2002 07:50 PDT
Infoscout:
My apologies, but there is one more request--if possible. On the bar
charts/pictorial graphs it would be wonderful if I could have the
years listed on the bottom--that would make them considerably more
useful. Again, in order of importance: DJIA, SP 500, SP100.
I hope you don't hate me at this point!
Best,
Marshall
|
Request for Answer Clarification by
recoveringtypea-ga
on
22 May 2002 13:57 PDT
Infoscout:
I figured out how to download the files to Excel [embarrassingly
easy], but I can't figure out to sort the columns I want to sort [the
monthly, bi-monthly, e-monthly etc returns] because those are reliant
on other data. Can you tell me how to do this or do it for me? Coming
up with that sorting was THE Key component/info I was looking for in
this data [getting a chart with the historical info I already had].
thanks.
|
Clarification of Answer by
infoscout-ga
on
22 May 2002 15:09 PDT
Hi Marshall,
I have finally got it all together!
DJIA:
Monthly Bimonthly Quarterly 4-monthly
< -10 24 25 24 24
from -10 to -8 15 9 8 6
from -8 to -6 31 13 9 9
from -6 to -4 54 34 19 10
from -4 to -2 82 39 26 13
from -2 to 0 156 52 31 17
from 0 to 2 185 57 26 23
from 2 to 4 142 57 36 17
from 4 to 6 99 54 26 18
from 6 to 8 39 37 25 15
from 8 to 10 20 30 23 22
> 10 21 27 36 43
S&P 500:
Monthly Bimonthly Quarterly 4-monthly
< -10 5 12 9 9
from -10 to -8 11 6 4 6
from -8 to -6 19 6 10 8
from -6 to -4 37 20 10 8
from -4 to -2 72 32 14 7
from -2 to 0 114 41 26 10
from 0 to 2 132 50 29 20
from 2 to 4 110 45 31 18
from 4 to 6 80 34 22 18
from 6 to 8 28 22 11 9
from 8 to 10 10 25 17 11
> 10 10 21 26 33
S&P 100:
Monthly Bimonthly Quarterly 4-monthly
< -10 2 4 3 3
from -10 to -8 6 2 1 1
from -8 to -6 4 1 1 3
from -6 to -4 11 7 4 3
from -4 to -2 28 6 4 2
from -2 to 0 36 16 10 3
from 0 to 2 52 23 12 9
from 2 to 4 43 20 11 5
from 4 to 6 29 8 8 6
from 6 to 8 17 10 5 2
from 8 to 10 4 11 6 8
> 10 4 10 13 14
I have uploaded the files again, and they contain the above info too.
Please download them again. The "1950" in the DJIA Quarterly chart was
a typo; I have corrected it. The latest charts have the year at the
bottom as you asked.
I am assuming that once you have the above info, you wouldn't need to
sort the columns anymore. I am not sure how to get the sorting to work
at the moment (I generated the above info without sorting), but if you
really need it, I can look into it more. Let me know..
Hope you find the data useful...
Thanks,
-Infoscout-ga.
|
Request for Answer Clarification by
recoveringtypea-ga
on
23 May 2002 05:59 PDT
Infoscout:
You be the man! That was excellent; thank you! I consider that you
have most definitely done your task/answered my question, but if you
want to go into total menschdom/bonus territory, I would like to know
how to do the sort function for the monthly % columns. Again, that's a
bonus, so only if you're up to it--but perhaps it would be nice for
you to know too.
I'm going to post a 5 star positive for you and hope this wasn't
frustrating for you [I'm curious how you figured out the break down
without sorting--unless [egads!] you counted it manually.
Also, for some reason, I was never notified when a
response/clarification from you came; I only found out by rechecking
it. [I know that's not your faulth, but something to contact Google
about.]
Anyhow, thanks again,
:)
Marshall
|
Clarification of Answer by
infoscout-ga
on
23 May 2002 17:21 PDT
Hi Marshall,
I don't know the best way to sort this, but here's a workaround:
Select the entire column you want to sort, then Edit->Copy it, and
then Edit->PasteSpecial, and select "Values" so that you paste just
the values (and not the formulas) to a new column. Then you can sort
this single new column without messing up rest of the data.
What I generated the data, I instead wrote an excel formula, which
would dump out a 1 if the value belonged to the given interval, and 0
otherwise. Then I just used another formula to add all the 1s.
Thanks for the rating!
Infoscout-ga.
|
Request for Answer Clarification by
recoveringtypea-ga
on
23 May 2002 18:11 PDT
Thanks so much. You took care of everything--soup to nuts! (And along
the way, I've learned a few things about using Excel.)
Best to you,
Marshall
|
