Tracking PCGS coin Values with Excel
slipgate
Posts: 2,301 ✭✭
I have had several inquiries regarding this excel spreadsheet, so this topic warrants being posted again. I've refined the process a little and have a sample you can PM me to get...
Copy this to notepad and save is as "MorganValue.iqy"
copy between the "****" but not including the "****"
****
WEB
1
http://www.pcgs.com/prices/frame.chtml?type=date&filename=morgan_regular
Selection=14
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
****
When you run the above file, it will open an excel spreadsheet and load the current PCGS coin values for that series. Make sure you save the resulting spreadsheet with the same name every time (MorganValue.xls) (that is, every time you want to update your collection's worth). Before I run and save a new value, I save the old one under the date as a file name so that I can trend my investment over time.
FYI: Occasionally PCGS will change the location of the start of the value chart. This is the "Selection=14" option in the above inquiry. You just need to play around with this number. Make it 12, or 13, or 15 for that matter. You can tell from what is generated on the spreadsheet what field in the web page it is reading. It is currently 14 for Morgan dollars but a month ago it was 12.
If you want to track other coins like washington quarters or mercury dimes, it is a simple matter to modify the Excel inquiry.
***Next***
Create an Excel "inventory" spreadsheet with your collection
Make a column for "current PCGS value"
link the contents of this cell to the appropriate cell in the sheet generated when you run the above Excel inquiry. The easiest way to do this is to click the "=" sign in the formula bar in teh "inventory" sheet, then click on the PCGS value spreadsheet (the one generated when you "double-click" the MorganValue.iqy file that you created above), then click the appropriate value cell for type, grade and year.
Once the spreadsheet is initially set up, it will not need to be setup again unless PCGS changes the format of their website.
FYI: With the way Excel works, you can add and remove coins, removing and adding rows or columns to the inventory spreadsheet, and Excel will update the formulas to reflect the new cell location so that your spreadsheet still "works" and pulls the correct value from the PCGS spreadsheet.
for advanced users: You should create a "sheet" in the excel document for each series that you collect. Then link to each of the value excel query results from the same spreadsheet, this way you have all the data that you need in one spreadsheet for trending and summarizing your entire collection.
Please DO NOT forget the 3 rules of computers:
1. backup
2. BACKUP
3. B A C K U P ! ! !
A good method is to MAIL yourself your finished spreadsheet and keep it in a folder in your email.
P.S. I tried to upload the files but the system would not allow me to upload any files - PM me and I'll email you the files you need, you can then modify them as needed.
Copy this to notepad and save is as "MorganValue.iqy"
copy between the "****" but not including the "****"
****
WEB
1
http://www.pcgs.com/prices/frame.chtml?type=date&filename=morgan_regular
Selection=14
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
****
When you run the above file, it will open an excel spreadsheet and load the current PCGS coin values for that series. Make sure you save the resulting spreadsheet with the same name every time (MorganValue.xls) (that is, every time you want to update your collection's worth). Before I run and save a new value, I save the old one under the date as a file name so that I can trend my investment over time.
FYI: Occasionally PCGS will change the location of the start of the value chart. This is the "Selection=14" option in the above inquiry. You just need to play around with this number. Make it 12, or 13, or 15 for that matter. You can tell from what is generated on the spreadsheet what field in the web page it is reading. It is currently 14 for Morgan dollars but a month ago it was 12.
If you want to track other coins like washington quarters or mercury dimes, it is a simple matter to modify the Excel inquiry.
***Next***
Create an Excel "inventory" spreadsheet with your collection
Make a column for "current PCGS value"
link the contents of this cell to the appropriate cell in the sheet generated when you run the above Excel inquiry. The easiest way to do this is to click the "=" sign in the formula bar in teh "inventory" sheet, then click on the PCGS value spreadsheet (the one generated when you "double-click" the MorganValue.iqy file that you created above), then click the appropriate value cell for type, grade and year.
Once the spreadsheet is initially set up, it will not need to be setup again unless PCGS changes the format of their website.
FYI: With the way Excel works, you can add and remove coins, removing and adding rows or columns to the inventory spreadsheet, and Excel will update the formulas to reflect the new cell location so that your spreadsheet still "works" and pulls the correct value from the PCGS spreadsheet.
for advanced users: You should create a "sheet" in the excel document for each series that you collect. Then link to each of the value excel query results from the same spreadsheet, this way you have all the data that you need in one spreadsheet for trending and summarizing your entire collection.
Please DO NOT forget the 3 rules of computers:
1. backup
2. BACKUP
3. B A C K U P ! ! !
A good method is to MAIL yourself your finished spreadsheet and keep it in a folder in your email.
P.S. I tried to upload the files but the system would not allow me to upload any files - PM me and I'll email you the files you need, you can then modify them as needed.
My Registry Sets! PCGS Registry
0
Comments
THANKS!!
What fields do you alter to make it pick up prices other than Morgans? Say Half Dimes? I tried but it would only pick up early half dimes.
Great transactions with oih82w8, JasonGaming, Moose1913.
<< <i>What fields do you alter to make it pick up prices other than Morgans? Say Half Dimes? I tried but it would only pick up early half dimes. >>
Yep, it seems to only pick up the first section of the price list.
For the proofs, change the "selection=" to "16". 14 gets the regulars, 15 gets the proof title and 16 gets the proof data.
Proofs:
****
WEB
1
http://www.pcgs.com/prices/frame.chtml?type=date&filename=seated_half_dimes
Selection=16
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
****