How To Create LIVE Financial Reports in Google Spreadsheet [VIDEO]

Static reports are dead.

Really. They’re physically dead [static (adj.) “lacking in movement, action, or change”] and metaphorically dead as financial advisors move toward live data reporting options.

Live financial data is easy to get your hands on. It’s HOW you use the live data that matters.

I recently recorded a conversation (below) with one advisor who uses live, publicly available data from Google Finance (similar to Yahoo Finance, etc) to build highly-customized reports in Google Spreadsheets. He also goes one step further and found a way to access live client portfolio data, too.

It’s a trick EVERY advisor can do. All you need is a Google account or the willingness to create one. If you want to pull in live data from clients’ portfolios (including their held-away accounts) like Jim does in the video, feel free to contact our team. We’d love to help.

If you’re a Blueleaf advisor, you can access live client data, too. Jim developed a way for ANY Blueleaf user to access client portfolio data in a Google Spreadsheet via the Blueleaf API. Watch the video to see what Jim does, and check out the resources listed below so you can do it, too.

Create LIVE Financial Reports in Google Spreadsheet

THE RECIPE

Ingredients:

– basic understanding of how spreadsheets work
– a Google account, or willingness to create one
– a Blueleaf account (optional)

Directions:

1.) Go to drive.google.com and ‘Create’ a new spreadsheet

2.) Pull in LIVE market data
-Use the GoogleFinance() functions to reference live market data.

Jim demonstrated these:
=GoogleFinance(“VBR”)
=SPARKLINE(GoogleFinance(“VBR”,”price”,TODAY()-90,TODAY()))

3.) Pull in LIVE client data (optional)

i. Implement the Blueleaf integration with Google Spreadsheets. Jim has enabled this using a ‘Google Script’. You can get his ‘Script’ code here: https://gist.github.com/jimkoch/8312098

ii. Create a “PARM” or “BlueleafData” tab to reference your Blueleaf advisor API token, an individual client’s Blueleaf ID#, and create a bank of their portfolio data. This will help you easily reference client portfolio data within the spreadsheet.

Jim demonstrated these:
=GetShareCountFromBlueleaf(<client id>,<account name>,”VBR”,<api token>)
=HYPERLINK(CONCATENATE(“https://<your site domain name>.blueleaf.com/home/select_active_user/”,<client id>)),”Click here!”)

4.) Format, design and share the info!

Resources:

http://bit.ly/1eQtgis – GoogleFinance() Function Description and Available Attributes
http://bit.ly/JYyNKl – Google Spreadsheet Complete Function List
http://bit.ly/1eQReu7 – Google Script Snippet for Blueleaf Integration

http://hub.am/1apbbZd – What’s the Blueleaf API?
http://hub.am/L5Fqe9 – Where can I find my Blueleaf API key?

http://hub.am/KjIFOq – Free 30-Day Trial of Blueleaf.com
http://hub.am/L5GEWO – Contact Blueleaf

Others:
http://bit.ly/1aCDDXW – Blueleaf API Documentation
http://bit.ly/19s34g1 – Google Apps Script Tutorials







Carolyn McRae Carolyn is Blueleaf’s in-house marketing guru. She writes on The Blueleaf Blog to make advisors’ lives easier, offering practice management and client engagement tips where and when they’re useful. Outside of the Blueleaf offices, she can be found running a 10k or cooking her famous chili. Chat LIVE with Carolyn on Twitter @BlueleafAdvisor!
  • ted everett

    Live reports may be well and good or rather exciting and dynamic but do they really add value to the conversation with clients? The risk of focusing on live data is that it can be distracting or encourage a very short term mindset that is detrimental to long term financial health. Clients who frequently check on account values more often than not tend to trade too frequently. The focus of the conversation should be on the big picture issues and strategies that drive the portfolio or financial plan, followed by specific actions or activities that flow from the larger discussion. Making sure you and the client leave the meeting with a full and mutual understanding of that big picture strategy is the goal. Thus, consider adding live data over static only if it won’t distract from that mission.

    • Great point Ted!

      However, it’s important to point out a critical distinction. Live Data can mean different things.

      1) Streaming – Data that is streaming like a ticker or is updated minute to minute for the purpose of seeing intraday changes.

      2) Automated – Data that is automatically connected and updated as the advisor needs/chooses for the purpose of eliminating work.

      Jim is referring to the latter and in that way case it’s no different than data that isn’t live except that it’s automated in a way that eliminates work allowing you to have more time to focus on the very conversations you rightly point out as critical for helping clients succeed.