Excel and Financial Info

Clauric

Mods, this has probably been done before, if so please merge.

I am trying to import a large amount of data regarding US stocks into Excel. I am currently using the XLQ - q matix system, based on the AAII system. However, I have found that it does not import a lot of the necessary data that I am looking for, especially from the quarterly/annual financials.

I have seen some suggested methods online, but they all require VBA/macros programming, which I can't do at the moment.

The main info I would need is:

Live current price
Historic prices (up to 5 years)
52 week high/low
Option availability
Last 7 annual financial reports
Last 8 quarterly financial reports

I was wondering if anybody had a sheet or knew of a simple method of downloading the info from Yahoo, Google, MSN, WSJ, etc.

freeze4real

Clauric wrote: »

Mods, this has probably been done before, if so please merge.

I am trying to import a large amount of data regarding US stocks into Excel. I am currently using the XLQ - q matix system, based on the AAII system. However, I have found that it does not import a lot of the necessary data that I am looking for, especially from the quarterly/annual financials.

I have seen some suggested methods online, but they all require VBA/macros programming, which I can't do at the moment.

The main info I would need is:

Live current price
Historic prices (up to 5 years)
52 week high/low
Option availability
Last 7 annual financial reports
Last 8 quarterly financial reports

I was wondering if anybody had a sheet or knew of a simple method of downloading the info from Yahoo, Google, MSN, WSJ, etc.

Hi,

I'm not sure if this what what you need. But klets say you need the monthly returns for IBM. Go to yahoo finance, historical prices then save as excel format. This will give you the historical prices.

But for live data I am niot sure how to do so with yahoo finanave, But its posible. If you get limited response on this forum go to the development forum. You'll find boardsies that use VBA/Excel a lot so this would be a walk in the park.

Out of interest are you doing masters/CFA ?

Grecco

To do it properly in excel you would need to use the MSN finance add-in for excel which I found unreliable. The next option would be to use macros but again this would involve downloading pre-built macros to do the tasks you need to do and exposing yourself to malware/viruses etc.

Best option would be to use the spreadsheet in google drive. Then use the
googlefinance() function

Hers is the info

https://support.google.com/drive/table/25273?hl=en

freeze4real

This is also a good video with excel VBA.

https://www.youtube.com/watch?v=iSlBE3CWg5Q

russtownsend

Can you clarify / quantify a little please ? How many stocks ? What, roughly, financial reports data do you need - just report date or the works ? How important is to have all the data you'd like or are you a little relaxed - The quantities of data for 1000 stocks and full reports will likely stretch the response times of excel to 30+ seconds - is that ok ?

Apologies if I seem picky, but I want to try and make sure I can get you what you need.

Are you prepared to pay AAII’s Stock Investor Pro $198 to get all of what you need ? Is is just a one off, of something you need regularly or a few times ?

I've only just joined so don't think I can PM you.

Clauric

russtownsend wrote: »

Can you clarify / quantify a little please ? How many stocks ? What, roughly, financial reports data do you need - just report date or the works ? How important is to have all the data you'd like or are you a little relaxed - The quantities of data for 1000 stocks and full reports will likely stretch the response times of excel to 30+ seconds - is that ok ?

Apologies if I seem picky, but I want to try and make sure I can get you what you need.

Are you prepared to pay AAII’s Stock Investor Pro $198 to get all of what you need ? Is is just a one off, of something you need regularly or a few times ?

I've only just joined so don't think I can PM you.

First of all the AAII SIP system does not work properly. Most of the data is there, and I like the fact that it provides up to 6 years data, and 8 quarters data, for about 5000 stocks, out of 7500+ on the US systems. However, it does not include a number of key data points, especially EBITDA and SAG expenses. Both of these are important when analysing stocks. They also do not provide depreciation in the income sheet, and provide it as part of cashflow.

The data I am looking for is 5 - 10 years worth of data for US, and ideally UK, German, French, Australian, etc stocks. The financial data, IB, BS and CFS would suffice. 5+ years worth of historical prices would also be necessary.

Any help would be gratefully appreciated.