Spreadsheet Tips

Discussion in 'General Trading Discussion' started by baudwalk, Nov 29, 2016.

  1. baudwalk

    baudwalk Senior Investor

    May 2015
    Likes Received:
    I use spreadsheets to do keep tabs on my portfolio and watchlists. I don't track individual transactions or dividends as the broker is now required to do that nitty-gritty detail. The Google Sheets spreadsheet is quite powerful, c.f. its GOOGLEFINANCE() function, with retrieval of live data. Functionality in the Chrome browser and the Android tablet/phone app is virtually identical.

    To that end, I've come up with a way to reference charts, research data and news directly from the spreadsheet. It involves a bit of string manipulation, but the principle is straightforward. Except for the broker, the website sources are free but some may require one to create an account and sign in. I find it easier and quicker to access the websites or apps directly from the spreadsheet rather than manually open a browser or app. Less navigation, less keystrokes, and less fussing with the keyboard or mouse works for me.

    Here are the Google Sheets cell entries. For my spreadsheets, stocks' tickers are in column B, starting in row 4, hence the cell reference to B4 in the following.

    ***If the line breaks in the browser you are using to view this post, note the URL portion of the cell entry (within the first pair of quotes) has no spaces.***


    Seeking Alpha
    =HYPERLINK("http://seekingalpha.com/symbol/"&$B4,"News +")





    And for those with Fidelity accounts, the following entries get me to the initial research pages. If not logged in, Fidelity will require you to do so before automatically launching the requested Fidelity page.


    Advanced Chart
    =HYPERLINK("https://screener.fidelity.com/ftgw/etf/gotoCL/snapshot/advancedChart.jhtml?symbols="&$B4&"&useHtml5=true","Adv Chart")

    These formulae also directly work in the Libreoffice Calc (.ods) spreadsheet, but do not port into an Excel (.xslx) spreadsheet. Calc does not have functions to retrieve live market data; I wouldn't expend any effort to bend Calc to my wants. I don't have Excel, so I can't convert the port into viable formulae. I'll leave it to an Excel user to modify the formulae; at least one won't have to manually retype the heart of each entry.

    Hope this helps. Your mileage may vary.
  2. JR Ewing

    JR Ewing Super Moderator Staff Member

    Feb 2014
    Likes Received:
    Good info - thanks!

Share This Page