An Excel Project

[Paffius]

Hello,

I am trying to do a project that is based in the analysis of stock quotes over several years. However I have a few questions and I hope you can help me.

The project

I want to analyze how certain stocks behave between specific dates, i.e, what is the percentage growth or decay between those specific dates. We are talking about more than 300 different companies over a period of 25 years. I am planning in getting the data from Yahoo finance, namely over the historical quotes options (where i can download the data). For exemple, for a stock like Microsoft, at some point i want to know what was the percentage change in the quote between the initial date of January 5th 1986 and October 2nd 1988.

The problems

The first problem is that i want to do the above study for several different date intervals and for all the 300+ companies in the exact same intervals.

1. When I download the individual data from Yahoo finance for all the companies, must i put it in 300+ different cheats within the same excel page? Or is there na easier alternative?

2. Is there a way to create a pivot table where i can reunite all the data from all the years and all the companies, and then i can analyze for each specific period how all the companies behave in terms of percentage chance?

3. If so, how can I automatize this so I don't need to create all the formulas for each specific company?

4. Is there a way to atribute a tittle to each of these periods (for example, between January 5th 1986 and October 2nd 1988 - imagine i can cal it Period 1) so I don't need to choose these dates one by one, but just the title of them?


I know these are many questions, but I really would like some help, namely on how i can start all this and which are my best options in terms of excel possibilities to do it.

Thank you so much.

 

[sheetspread]

25 years x 365 days x 300+ companies will likely exceed 3 million rows, and Excel 2007 and later only holds just over 1 million. So you can break it up into 3 tables and merge them with a union query in Power BI or SQL. Then pivot/filter/group by, etc.

 

[Paffius]

25 years x 365 days x 300+ companies will likely exceed 3 million rows, and Excel 2007 and later only holds just over 1 million. So you can break it up into 3 tables and merge them with a union query in Power BI or SQL. Then pivot/filter/group by, etc.

But, how?

I need some help on how to upload each of those tables to excel, as my knowledge of excel is limited.

 

[Joe4]

I need some help on how to upload each of those tables to excel, as my knowledge of excel is limited.

If your knowledge of Excel is limited, this could prove to be quite the challenge. It really sounds more like a database type of thing, so I question whether Excel is really even the right tool here, and maybe you should be using a database program like Access, SQL, MySQL, or Oracle (at the very least, to store the massive amounts of data).

You may want to consider enlisting the help of a consultant to help you work through all of what you want to do.
There are some Excel consultants listed here: https://www.mrexcel.com/consulting-services/

 

[sheetspread]

But, how?

I need some help on how to upload each of those tables to excel, as my knowledge of excel is limited.

Oh I thought you already figured out that part. As Joe4 said, Access/SQL Server/Oracle etc. is probably the best choice. If I were to do this in Excel, however, and wanted free historical stock quotes, the first place I'd look is here: http://investexcel.net/multiple-stock-quote-downloader-for-excel/ Note that Yahoo may have discontinued their bulk downloading feature, so I'm not sure if it still works (the author mentions something about now connecting to Google). If you are able to download, then try to set up 3 tables. From there you can apply the techniques I mentioned above.

 

[Paffius]

Oh I thought you already figured out that part. As Joe4 said, Access/SQL Server/Oracle etc. is probably the best choice. If I were to do this in Excel, however, and wanted free historical stock quotes, the first place I'd look is here: http://investexcel.net/multiple-stock-quote-downloader-for-excel/ Note that Yahoo may have discontinued their bulk downloading feature, so I'm not sure if it still works (the author mentions something about now connecting to Google). If you are able to download, then try to set up 3 tables. From there you can apply the techniques I mentioned above.

I will try it.

Another question: which do you think is the best way to add another filter that is not part of the original data? What i mean is that i can only download the dates and closing prices of each stock, but how do I associate and industry or sector to each stock? For exemple, Microsoft is part of the Technology Sector.

 

[sheetspread]

You can find a list like here:

https://en.wikipedia.org/wiki/List_of_S&P_500_companies

and vlookup (or inner join or write a DAX Related function.......)

 

[Paffius]

You can find a list like here:

https://en.wikipedia.org/wiki/List_of_S&P_500_companies

and vlookup (or inner join or write a DAX Related function.......)

I know about that list, however of those companies im about to analyze, many aren't part of the S&P500. So, i wanted a way to associate the company to a specific sector directly in excel. Need I to create a column for that?

 

[sheetspread]

Then you can check the Russell 2000 Index, etc (or a mutual fund annual report that has the companies and sectors). If there are still ones you can't find, then yes, add them, but most should be available.

 

[Paffius]

Once again thank you.

I will keep bothering you guys as i evolve the project.