I ganked your spreadsheet man, thanks for all that work. I was working on a spreadsheet but it was Barney-style compared to some of the majestic pieces you guys made.hoost wrote: snip
Spreadsheets to Track PP
Moderator: Global Moderator
- Kriegsspiel
- Executive Member
- Posts: 4052
- Joined: Sun Sep 16, 2012 5:28 pm
Re: Spreadsheets to Track PP
You there, Ephialtes. May you live forever.
Re: Spreadsheets to Track PP
No worries. I'm glad someone else was able to make sense of/use it. It makes the effort more worthwhile. If you have any questions on tweaking it, let me know.Kriegsspiel wrote:I ganked your spreadsheet man, thanks for all that work. I was working on a spreadsheet but it was Barney-style compared to some of the majestic pieces you guys made.hoost wrote: snip
- Kriegsspiel
- Executive Member
- Posts: 4052
- Joined: Sun Sep 16, 2012 5:28 pm
Re: Spreadsheets to Track PP
Yea, the part that blew my mind was that you could link to websites to update the cells. I never knew you could do that. I don't understand how it updates my bonds... that's pretty much black wizardry. I did figure out how to update the Colorado gold table since I guess they changed the rows since you made that table. Other than that, I pretty much kept it as is, except for deleting out the stuff I don't use. I'll probably have to recheck that original shared one if I need to add that stuff back in... I think I deleted one of your really long formulae.
I think the only thing I might add to it is something to calculate the duration of the bond ladders.
I think the only thing I might add to it is something to calculate the duration of the bond ladders.
You there, Ephialtes. May you live forever.
Re: Spreadsheets to Track PP
Hello,
where can I see daily variation of HBPP?
I would like to feel the flutuactions day by day in a chart.
Thank you.
where can I see daily variation of HBPP?
I would like to feel the flutuactions day by day in a chart.
Thank you.
Live healthy, live actively and live life!
Re: Spreadsheets to Track PP
A quick way to do this would be make a Google Finance Page and put a hypothetical $2500 in each of IAU, SHY, TLT, and VTI.
Background: Mechanical Engineering, Robotics, Control Systems, CAD Modeling, Machining, Wearable Exoskeletons, Applied Physiology, Drawing (Pencil/Charcoal), Drums, Guitar/Bass, Piano, Flute
"you are not disabled by your disabilities but rather, abled by your abilities." -Oscar Pistorius
"you are not disabled by your disabilities but rather, abled by your abilities." -Oscar Pistorius
Re: Spreadsheets to Track PP
yes GREAT
Can you only see ONE year chart?
Regards
Can you only see ONE year chart?
Regards
Live healthy, live actively and live life!
Re: Spreadsheets to Track PP
Well you can either plot all 4 of the items and compare them against each other. This will at least show you percentage changes in the past year.
Or check the price one year ago and state that as your cost basis for each of the 4 items. That way you'll have the price appreciation of one year.
Or check the price one year ago and state that as your cost basis for each of the 4 items. That way you'll have the price appreciation of one year.
Background: Mechanical Engineering, Robotics, Control Systems, CAD Modeling, Machining, Wearable Exoskeletons, Applied Physiology, Drawing (Pencil/Charcoal), Drums, Guitar/Bass, Piano, Flute
"you are not disabled by your disabilities but rather, abled by your abilities." -Oscar Pistorius
"you are not disabled by your disabilities but rather, abled by your abilities." -Oscar Pistorius
Re: Spreadsheets to Track PP
Yahoo Finance has longterm (+10years) charts for the similar My Portfolio?
Tks
Tks
Live healthy, live actively and live life!
Re: Spreadsheets to Track PP
I just found this online: lovely way to track physical gold (and silver) coin purchases:
https://drive.google.com/previewtemplat ... ode=public#
https://drive.google.com/previewtemplat ... ode=public#
- Kriegsspiel
- Executive Member
- Posts: 4052
- Joined: Sun Sep 16, 2012 5:28 pm
Re: Spreadsheets to Track PP
hoost, is there a website where I can link a cell in the gold portion to the gold spot price? I had been using Colorado gold, and tried to link it to Kitco's site but messed something up.
You there, Ephialtes. May you live forever.
Re: Spreadsheets to Track PP
Here is the bid from APMEX: =Index(ImportHTML("http://www.apmex.com","table",0),2,2)Kriegsspiel wrote: hoost, is there a website where I can link a cell in the gold portion to the gold spot price? I had been using Colorado gold, and tried to link it to Kitco's site but messed something up.
I use colorado gold for the bid on coins. This will give you the full table: =importhtml("http://www.coloradogold.com/selling.php",1,0)
To get an individual value from the table, use this (this is for 1 oz. gold eagle): =index(importhtml("http://www.coloradogold.com/selling.php",1,0),2,2)
1 oz. Maple Leaf would be: =index(importhtml("http://www.coloradogold.com/selling.php",1,0),8,2)
Is that what you're looking for?
- Kriegsspiel
- Executive Member
- Posts: 4052
- Joined: Sun Sep 16, 2012 5:28 pm
Re: Spreadsheets to Track PP
=index(importhtml("http://www.kitco.com/market/",1,0),5,6)
That's the one I'm using to get the gold spot price, just for a consistent measure across the different coins/dealers one might use. It wasn't working for a while so I thought I broke the internet, but it's working now.
That's the one I'm using to get the gold spot price, just for a consistent measure across the different coins/dealers one might use. It wasn't working for a while so I thought I broke the internet, but it's working now.
You there, Ephialtes. May you live forever.
Re: Spreadsheets to Track PP
Hi guys, just wanted to say thanks for putting up all the info for tracking so far and had a question. Fidelity recently updated it so that the old =IMPORTHTML for bond prices isn't pulling in the price anymore. Does anyone know another place to pull this info from and/or can write up a =IMPORTHTML formula please?
Re: Spreadsheets to Track PP
I finally found a work around for the bond pricing issue.
This line pulls the price for 912810QU5:
=Index(importhtml("http://cxa.marketwatch.com/finra/BondCe ... ,"table",0),2,2)*10
This line pulls the price for 912810RC4:
=Index(importhtml("http://cxa.marketwatch.com/finra/BondCe ... ,"table",0),2,2)*10
To figure out the correct code for your particular bond, visit http://cxa.marketwatch.com/finra/BondCe ... EyODEwUVU1.
Enter your CUSIP in the text input and search for it. Then change the code appropriately. So "OTEyODEwUkM0" would be the thing to change in the first listed spreadsheet formula.
If anyone can either figure out the algorithm used to change cusips to the web address, or write a scipt to automate this, I'm sure it will be appreciated by all.
***EDIT***
The above is no longer working.
See the linked solution by fnord123:
http://gyroscopicinvesting.com/forum/bo ... /#msg77539
This line pulls the price for 912810QU5:
=Index(importhtml("http://cxa.marketwatch.com/finra/BondCe ... ,"table",0),2,2)*10
This line pulls the price for 912810RC4:
=Index(importhtml("http://cxa.marketwatch.com/finra/BondCe ... ,"table",0),2,2)*10
To figure out the correct code for your particular bond, visit http://cxa.marketwatch.com/finra/BondCe ... EyODEwUVU1.
Enter your CUSIP in the text input and search for it. Then change the code appropriately. So "OTEyODEwUkM0" would be the thing to change in the first listed spreadsheet formula.
If anyone can either figure out the algorithm used to change cusips to the web address, or write a scipt to automate this, I'm sure it will be appreciated by all.
***EDIT***
The above is no longer working.
See the linked solution by fnord123:
http://gyroscopicinvesting.com/forum/bo ... /#msg77539
Last edited by hoost on Tue Sep 17, 2013 10:27 am, edited 1 time in total.
-
- Executive Member
- Posts: 527
- Joined: Mon Aug 24, 2015 3:12 pm
Re: Spreadsheets to Track PP
Is this still the most up-to-date Hoost-sheet?
https://docs.google.com/spreadsheet/ccc ... sp=sharing
https://docs.google.com/spreadsheet/ccc ... sp=sharing
-
- Executive Member
- Posts: 156
- Joined: Tue Apr 26, 2011 7:15 pm
Re: Spreadsheets to Track PP
Hi, anyone have a link to track the gold spot price?
Because my link to apmex is broken.
Because my link to apmex is broken.
Re: Spreadsheets to Track PP
=importxml("http://www.jmbullion.com/charts/gold-price/","//*[@id='gounce']")escafandro wrote:Hi, anyone have a link to track the gold spot price?
Because my link to apmex is broken.
-
- Junior Member
- Posts: 5
- Joined: Fri Dec 20, 2013 4:45 pm
Re: Spreadsheets to Track PP
http://www.apmex.com/ no longer lists gold prices on their main page....You should be able to just change it out for http://www.apmex.com/category/10000/gold and It should start working again.
If not here is what i've been using:
=ImportXml("http://www.apmex.com/category/10000/gold","//tr[td='Gold']/td/span[@class='item-bid']")
If not here is what i've been using:
=ImportXml("http://www.apmex.com/category/10000/gold","//tr[td='Gold']/td/span[@class='item-bid']")
Re: Spreadsheets to Track PP
I've been using these for tracking silver and gold prices.
=IF(1, ImportXml("http://services.packetizer.com/spotprices/?f=xml", "/SpotPrices/silver"), 18)
=IF(1, ImportXml("http://services.packetizer.com/spotprices/?f=xml", "/SpotPrices/gold"), 1300)
=IF(1, ImportXml("http://services.packetizer.com/spotprices/?f=xml", "/SpotPrices/silver"), 18)
=IF(1, ImportXml("http://services.packetizer.com/spotprices/?f=xml", "/SpotPrices/gold"), 1300)
- dualstow
- Executive Member
- Posts: 14292
- Joined: Wed Oct 27, 2010 10:18 am
- Location: synagogue of Satan
- Contact:
Re: Spreadsheets to Track PP
Ah, I didn't see the June posts above when I was simultaneously posting in the Gold §.
viewtopic.php?f=5&t=6756&p=162145#p162145
I guess I need a new code for SPX (S&P 500), as I was using Apmex code.
(Cool, Greg's still here!)
viewtopic.php?f=5&t=6756&p=162145#p162145
I guess I need a new code for SPX (S&P 500), as I was using Apmex code.
(Cool, Greg's still here!)
9pm EST Explosions in Iran (Isfahan) and Syria and Iraq. Not yet confirmed.
Re: Spreadsheets to Track PP
And how do I get a chart from this portfolio? I know nothing about spread sheets. Over the years I've enjoyed checking: https://www.google.com/finance/chart?ch ... hy&tlf=12h every day. Now it is not working anymore.Greg wrote:A quick way to do this would be make a Google Finance Page and put a hypothetical $2500 in each of IAU, SHY, TLT, and VTI.
I'm always interested in graphically seeing how the different elements relate on a daily basis.
So with a hypothetical portfolio, is there an easy way to see it graphically in a chart? Thanks in advance.
Re: Spreadsheets to Track PP
I was using a spreadsheet in Apple's Numbers for a long time. I had columns for each asset class and rows for each portfolio of mine (IRA, Roth, etc.). I would add the current price of the ticker I held for each asset class and have my total row at the bottom multiply the number of shares I held by the current price to get the current value. Then, I'd color code the columns to show whether they were outside of my delta ranges for rebalancing. Personally, I like AlphaVantage for quotes, their API is free.
It worked pretty well but being a software engineer I started writing code to automate it. I was using it myself for some time but decided to clean it up for others. Check it out if you'd like at MyHoldings.io.
It worked pretty well but being a software engineer I started writing code to automate it. I was using it myself for some time but decided to clean it up for others. Check it out if you'd like at MyHoldings.io.
Re: Spreadsheets to Track PP
Thank you Sir!!
Re: Spreadsheets to Track PP
I made this sample for a friend last November 2017, You can download it and edit it for your own purposes and upload to your own google account.
https://docs.google.com/spreadsheets/d/ ... sp=sharing
https://docs.google.com/spreadsheets/d/ ... sp=sharing
Re: Spreadsheets to Track PP
Steve,
Do you have long term data showing the performance over decade or more. If so can you share exactly what you used and how you obtained the data.
Do you have long term data showing the performance over decade or more. If so can you share exactly what you used and how you obtained the data.