Hi - I'm building some tools in Google Sheets to help me analyze stocks, but I'm stuck and so hoping for some help. A book suggestion for how to build something like this would be best, otherwise a website would be great too.
At the moment I have managed to put together a general "portfolio analyzer" tool that helps with rebalancing, calculates yield, etc. No shortage of these around to copy from
I want to build something that allows me to 1) feed it a list of stocks, 2) specify some criteria eg Market Cap/PE/etc to FILTER on, and also 3) specify some other criteria eg ROA/PE/etc to RANK the list of stocks on, and finally 4) weight each ranking criteria, so that I end up with 5) a list of stocks filtered and ranked based on the weighted criteria.
I've been trying to build it in Google Sheets. I can get a daily list of stock tickers into a sheet, and then use =GoogleFinance() to finger some basic market data, eg Market Cap, PE.
The things I'm struggling with are:
1) filtering. I've got a sheet of "constants" that I use to set parameters eg minimum Market Cap, but I'm struggling to come up with a way to filter on the fly. It feels like I should be using some kind of SQL query. I like Google Sheets because it's a nice simple tool but I'm not married to it, and would use a web tool if a suitable one were available, or otherwise I'm open to building something myself.
2) financial data. Google Sheets has enough price-sensitive data (eg PE). But it doesn't have any data from the financial statements or ratios eg ROIC. I'm sure this data is out there but I just can't find a way to get it into my Sheet.
Anyone who has experience in putting something similar together, I'd love to hear your suggestions as to how I should be approaching this. I've got some ideas for other tools but I want to try and get this one sorted out first.