The Money Mustache Community

Learning, Sharing, and Teaching => Investor Alley => Topic started by: centastic on February 02, 2018, 03:09:29 AM

Title: Building Some Tools
Post by: centastic on February 02, 2018, 03:09:29 AM
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.
Title: Re: Building Some Tools
Post by: ILikeDividends on February 02, 2018, 06:26:06 PM
Google this: "10 Web Scraping Tools to Extract Online Data - Hongkiat"

Pick a tool.  Pick a website.  Scrape away.

As stated in the article, "web scraping is the new data entry technique."

If you are a propeller head (like me) you can build your own scraper.  But these days your time is probably better invested investigating existing available scrapers before going the DIY route.

Caveat: Be careful who and how you scrape.  Scrape Google search results too intensely, and you can expect your IP address to be banned by Google.

Also, MySQL is a popular FREE and very robust and full featured SQL database (now owned and supported by Oracle) that you can embed in your website, or install on your PC:

https://www.mysql.com/downloads/

Included options are Excel extensions that allows your Excel spreadsheet to suck data into a worksheet  sourced from a local MySQL database.

So, using Yahoo Finance as a source, here's a rough architecture for you:

Your stock symbols --> Scrape Yahoo Finance --> MySql Database --> SQL Views for filtering --> Your filtered results --> Excel worksheet.
Title: Re: Building Some Tools
Post by: swashbucklinstache on February 02, 2018, 07:34:03 PM
Also along the lines of customizing, especially if using databases, there might be APIs from data aggregators like morningstar, google, yahoo finance that you might be able to use instead of scraping? Requires less babysitting, probably, if they exist and bonus points if they are free.
Title: Re: Building Some Tools
Post by: centastic on February 03, 2018, 11:29:46 PM
Thanks. I'm happy to pay a little for the data but unfortunately some of those are a little out of my price range and I'm not actually sure they'll have the data I need. A Bloomberg license would have the data, AND would be a good amount of babysitting for me but again too expensive. Having a look around, there seem to be a few data providers that give access to varying amounts of data popping up recently - quandl and intrinio would be two I came across recently - but nothing as good as say Compustat

I'm surprised there isn't a generic spreadsheet or free service that already exists that allows you filter and rank based on weighted factors.
Title: Re: Building Some Tools
Post by: centastic on February 04, 2018, 12:59:53 AM
Also Alphavantage
Title: Re: Building Some Tools
Post by: ILikeDividends on February 04, 2018, 12:44:28 PM
I'm surprised there isn't a generic spreadsheet or free service that already exists that allows you filter and rank based on weighted factors.
I wouldn't imagine that maintaining an authoritative up-to-date set of financial metrics is cheap or easy to do, so I'd be surprised if anyone who went to that expense would just give the raw data away for free.

It's not in a spreadsheet, but free screeners abound:

https://www.msn.com/en-us/money/stockscreener

If Schwab is your broker, they offer an even more extensive screening solution that is available to any trading account.

You can create screens from among their existing filters.  But if you want to create your own filters and weightings and ranking methods, then you'll probably have to  source the data somehow, and build a DIY solution yourself.

ETA: Google "free stock screeners" for other options.

You might well find that what you want to do can already be done quite adequately, and without getting your hands directly on the underlying data.
Title: Re: Building Some Tools
Post by: centastic on February 08, 2018, 07:06:02 PM
I'm surprised there isn't a generic spreadsheet or free service that already exists that allows you filter and rank based on weighted factors.
I wouldn't imagine that maintaining an authoritative up-to-date set of financial metrics is cheap or easy to do, so I'd be surprised if anyone who went to that expense would just give the raw data away for free.

Ok but what about without the data?

I'm struggling with even the basics at the moment, ie how to set up a spreadsheet that will do weighted rankings, regardless of the data. I've finally managed to make something, but it's not very flexible eg with regards to the number of variables or their (respective) weightings.

Title: Re: Building Some Tools
Post by: ILikeDividends on March 08, 2018, 05:06:06 PM
I'm surprised there isn't a generic spreadsheet or free service that already exists that allows you filter and rank based on weighted factors.
I wouldn't imagine that maintaining an authoritative up-to-date set of financial metrics is cheap or easy to do, so I'd be surprised if anyone who went to that expense would just give the raw data away for free.

Ok but what about without the data?

I'm struggling with even the basics at the moment, ie how to set up a spreadsheet that will do weighted rankings, regardless of the data. I've finally managed to make something, but it's not very flexible eg with regards to the number of variables or their (respective) weightings.

Assuming you use Excel, check out this article:

Use SUMPRODUCT to calculate weighted averages
https://www.gcflearnfree.org/excel-tips/use-sumproduct-to-calculate-weighted-averages/1/

You can enhance flexibility by 1) how you organize your data, and 2) by writing your formulas to be insensitive to the length of the vertical dimension.  E.g., using the example in the article, rather than writing this for your "Final Grade" formula:

=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)

you would write this:

=SUMPRODUCT(B:B, C:C)/SUM(C:C)

The advantage is that the formula would work the same no matter how many (or few) lines of data you have.

The slight disadvantage is that you could not have any other type of numerical data in the same verticals (B, or C colums).  So, again using the article's example, you couldn't put your "Final Grade" into the B10 cell.  That's a small price to pay for the increased flexibility.  Put your Final Grade formula into any other column, except B or C, and the modified formula will work fine, as intended.

Your screen, of course, will have multiple filters.  So extend horizontally, to the right, pairs of columns; each pair representing each additional filter.  You might find it easier to put each corresponding "Final Grade(s)" formulas (one for each filter) into a separate worksheet, and build your final screening logic out from there.
Title: Re: Building Some Tools
Post by: swisswise on March 10, 2018, 12:46:49 PM
https://docs.google.com/spreadsheets/d/1NVslJIBz3gY19Nz0uURerDV4ZmalV_J9H8nYaTLITJ4/edit?hl=en&hl=en#gid=1
Title: Re: Building Some Tools
Post by: centastic on March 12, 2018, 04:56:34 AM
ZOMG thanks! Looking into this now...