spreadsheet macro to track/chart daily porfolio value

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
Post Reply
User avatar
Pocket Cruiser
Posts: 144
Joined: Mon Jul 08, 2013 5:48 pm
Location: East Texas

spreadsheet macro to track/chart daily porfolio value

Post by Pocket Cruiser » Wed Jun 01, 2016 4:40 pm

In one of the cells in my spreadsheet, I have the total current value of my portfolio, which changes throughout the day as prices change.

I'd like to be able to see the changes in value of my total portfolio in a daily chart. Is there a way to do this in google finance? Is there maybe a macro that could be built that snapshots the value of my portfolio daily?

Globalviewer58
Posts: 428
Joined: Fri Jul 18, 2008 3:26 pm

Re: spreadsheet macro to track/chart daily porfolio value

Post by Globalviewer58 » Wed Jun 01, 2016 4:47 pm

Here's a link to a thread that contains a portfolio tracking spreadsheet in Google docs. The spreadsheet can be customized to your portfolio holdings. With one click you will see current positions. This was posted by Femur in 2009. viewtopic.php?f=1&t=38755

dubsem
Posts: 189
Joined: Tue Jan 27, 2015 1:25 pm
Location: CA

Re: spreadsheet macro to track/chart daily porfolio value

Post by dubsem » Wed Jun 01, 2016 4:49 pm

You're on the right track with the macro that can pull data from an internet source. Unfortunately, I don't know how to build it.
G.O.O.D.

BH13
Posts: 43
Joined: Thu Oct 20, 2011 2:38 pm

Re: spreadsheet macro to track/chart daily porfolio value

Post by BH13 » Wed Jun 01, 2016 5:08 pm

Yes, I have graphs that track both 5 min intervals during the day, as well as a daily close graph in my Google Sheets portfolio tracker. This is done using Google Scripts. Let me anonymize and share a version of my sheet this eve.

User avatar
Pocket Cruiser
Posts: 144
Joined: Mon Jul 08, 2013 5:48 pm
Location: East Texas

Re: spreadsheet macro to track/chart daily porfolio value

Post by Pocket Cruiser » Wed Jun 01, 2016 5:14 pm

Thanks for the responses.

That would be awesome BH13!

BH13
Posts: 43
Joined: Thu Oct 20, 2011 2:38 pm

Re: spreadsheet macro to track/chart daily porfolio value

Post by BH13 » Wed Jun 01, 2016 7:48 pm

Here is my anonymized Google Sheet:

https://docs.google.com/spreadsheets/d/ ... dit#gid=32

The spreadsheet has two sheets (Daily AA & Historical). These sheets are called by name in the three linked scripts. To see the scripts (1) File > Make a copy of the sheet (2) go to Tools > Script Editor

Script Descriptions:
====================
CaptureDailyTotal.gs - Copies Total value to last empty row of sheet Historical. Script triggered to run at 4pm daily

ClearYesterdayColumns.gs - Clears Historical sheet columns F & G triggered daily at 6a to clear prior days numbers

TotalsPerMin.gs - Copies Total value to last empty row of sheet Historical column F (time) & G (Total). Triggered to run every 5 min between 9a - 4p

On the Script Editor page, you can create triggers by clicking on the Clock icon. My triggers did not copy to this anonymized shared spreadsheet.

Hope that helps & you can incorporate these.

dcabler
Posts: 312
Joined: Wed Feb 19, 2014 11:30 am

Re: spreadsheet macro to track/chart daily porfolio value

Post by dcabler » Thu Jun 02, 2016 10:28 am

I'm an Excel user and use the smf addin. It's available by subscribing to smf_addin in yahoo groups.

nolapepper
Posts: 170
Joined: Wed Mar 25, 2015 3:06 pm

Re: spreadsheet macro to track/chart daily porfolio value

Post by nolapepper » Thu Jun 02, 2016 11:31 am

Do you mind posting the template you used?

I use Excel and have to manually update the price which is really a hassle.

Thanks!!

dcabler wrote:I'm an Excel user and use the smf addin. It's available by subscribing to smf_addin in yahoo groups.

User avatar
wizzard
Posts: 73
Joined: Fri May 20, 2016 12:32 pm

Re: spreadsheet macro to track/chart daily porfolio value

Post by wizzard » Thu Jun 02, 2016 11:44 am

^^^ Same here, I would love to see it done on Excel too!

**Joined the group on Yahoo, but I am still looking for the right file to download

https://beta.groups.yahoo.com/neo/group ... rer=zenway

http://ogres-crypt.com/SMF/


I just installed the add-in and it was a nightmare for me, I keep getting a NAME error on Excel. This is not very user friendly and I already spent too much time on it. Good luck for anyone trying this method!
Last edited by wizzard on Thu Jun 02, 2016 12:44 pm, edited 2 times in total.
"In uncertain times, show equanimity. Otherwise you are an unfit shareholder" -Charlie Munger

nolapepper
Posts: 170
Joined: Wed Mar 25, 2015 3:06 pm

Re: spreadsheet macro to track/chart daily porfolio value

Post by nolapepper » Thu Jun 02, 2016 11:45 am

Thanks for sharing! I was able to find the “missing columns" after opening it in a separate window.


BH13 wrote:Here is my anonymized Google Sheet:

https://docs.google.com/spreadsheets/d/ ... dit#gid=32

The spreadsheet has two sheets (Daily AA & Historical). These sheets are called by name in the three linked scripts. To see the scripts (1) File > Make a copy of the sheet (2) go to Tools > Script Editor

Script Descriptions:
====================
CaptureDailyTotal.gs - Copies Total value to last empty row of sheet Historical. Script triggered to run at 4pm daily

ClearYesterdayColumns.gs - Clears Historical sheet columns F & G triggered daily at 6a to clear prior days numbers

TotalsPerMin.gs - Copies Total value to last empty row of sheet Historical column F (time) & G (Total). Triggered to run every 5 min between 9a - 4p

On the Script Editor page, you can create triggers by clicking on the Clock icon. My triggers did not copy to this anonymized shared spreadsheet.

Hope that helps & you can incorporate these.

User avatar
Pocket Cruiser
Posts: 144
Joined: Mon Jul 08, 2013 5:48 pm
Location: East Texas

Re: spreadsheet macro to track/chart daily porfolio value

Post by Pocket Cruiser » Thu Jun 02, 2016 3:33 pm

BH13 wrote:Here is my anonymized Google Sheet:

https://docs.google.com/spreadsheets/d/ ... dit#gid=32

The spreadsheet has two sheets (Daily AA & Historical). These sheets are called by name in the three linked scripts. To see the scripts (1) File > Make a copy of the sheet (2) go to Tools > Script Editor

Script Descriptions:
====================
CaptureDailyTotal.gs - Copies Total value to last empty row of sheet Historical. Script triggered to run at 4pm daily

ClearYesterdayColumns.gs - Clears Historical sheet columns F & G triggered daily at 6a to clear prior days numbers

TotalsPerMin.gs - Copies Total value to last empty row of sheet Historical column F (time) & G (Total). Triggered to run every 5 min between 9a - 4p

On the Script Editor page, you can create triggers by clicking on the Clock icon. My triggers did not copy to this anonymized shared spreadsheet.

Hope that helps & you can incorporate these.
Thanks! That was exactly what I was looking for.

Post Reply