Free Live Financial Pivot Tables for Stocks and Indexes using Google Finance

Live data feeds (in general) cost money. Bloomberg Terminals costs are far beyond most non-professional investors' means. But slowly a data revolution is starting, with Google leading way. And the aim is to create the Poor Man's Trading Terminal.

We're a great fan of Google Apps here at Scalabiliti. We use it for all our documentation, and for sharing reports and information with our clients.

Recently, Google Apps Spreadsheet gained a very intersesting new function named googlefinance(). As you probably already know, this funciton allows you to access both live and historical pricing from Google Finance straight into your spreadsheet. The best bit is that it's live (or at least as live as the backend data), and it auto-updates when the spreadsheet is open in your browser window.

One of clients actively trades CFD's through out the day. The client is small-scale, but through the leverage power of CFD's is able to make a good return each day, if they correctly guess the movement of the market.

One tool the client uses to predict each days movement is through the use of Pivot Tables. Using the previous days values (Open, High, Low and Close) Pivot tables, based on the calculation set used, give you four support and resistance levels either side of the supposed centre value. You can find an example of the calculation here: Pivot Table Calculator.

Each day, the client would enter these values by hand into their Excel spreadsheet, and out would pop the answer. Admittedly a five minute job, and as the client was only trading a single index (at present) it was only five minutes a day. But what about when they wanted to scale?

Taking the Pivot Tables calculations, we built (in 10 minutes) an auto-updating Pivot table, that showed all their currently used calculations, plus they could replace the ticker value with any valid Google Finance value to get the live results. Now the client can check the Pivot table, and support and resistance levels for any asset they want, all automated and up to date, without breaking a sweat. This has allowed them to greatly increase their coverage of the market, and has been working well so far. We also hooked up an update script (Javascript Macro) within Google Spreadsheet which updates the colour of the Support/Resistance calculations to Red or Green based on the current market price. They can see quickly and easily where the current value is within the calculated values.

Another distinct advantage of this approach is usabilitiy. Because it's on Google Apps, the client can share it around all of their traders. If anyone spots a problem, or sees a potential upgrade, it can be changed immediately and everyone is up-to date again. With their gracious permission, you can now see a live updating version using this link to a copy of their spreadsheet: Shared Financial Live Pivot Table - Scalabiliti

Ultimately, this is no replacement for a true Trading Screen. But it does show what can be accomplished very, very quickly for end users. Any user of the Google Spreadsheet with even a rudimentary knowledge of Finance Markets and Spreadsheets could create something which could give them a little bit of an edge. Is that what we're all looking for?

Feedback is welcome to steven.algieri@scalabiliti.com, plus please feel free to leave your comments below.