Useful Excel Files for coin inventories

slipgate · February 9, 2006 11:28PM

My father wrote this very simple Excel inquiry that goes out and grabs what is on PCGS' site and creates an excel spreadsheet of it. I use it to keep track of the PCGS value of my collection. It is setup for Morgan Dollar's right now, but could easily be modified for any type of coin. I am not an excel expert but the script would be a good starting point for an inventory sheet of all coins. It would need a few more macros to make the raw data more useful.

Just copy the below (between but not including the stars) into notepad and save as "morganprices.iqy". Then double click it. It will open up an excel spreadsheet and load web site contents.

*************
WEB
1
http://www.pcgs.com/prices/frame.chtml?type=date&filename=morgan_regular

Selection=12
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
*************

jdsinva · February 9, 2006 11:42PM

Works cool!

SmallSizedGuy · February 9, 2006 11:43PM

Thank you!

Worked OK for me!

jdsinva · February 9, 2006 11:58PM

I did a little more exploring in Excel and found how to do this. This is quite powerful and I see a number of other uses for this type of functionality. . .

Thanks for sharing!!!

66Tbird · February 10, 2006 12:01AM

Thanks, That was cool.

tmot99 · February 10, 2006 12:06AM

Very cool and easily changable to other series.

mozin · February 10, 2006 1:33AM

Doesn't work at all for me, and I don't have a notepad. There must be a simply way for we Mac users to do this.

numonebuyer · February 10, 2006 1:38AM

Excellent.

Thanks

19Lyds · February 10, 2006 1:42AM

Suh-weet. Who do I send the check to cause this will save me a ton of insane time?

jdsinva · February 10, 2006 1:52AM

<< Doesn't work at all for me, and I don't have a notepad. There must be a simply way for we Mac users to do this. >>

Notepad has nothing to do with it but you do need to have MS Excel installed. It can be saved in any text editor but it must be saved with the .iqy extension. If you have MS Excel installed, then your system should recognize the file as an Excel query file and do it's thing.

slipgate · February 10, 2006 1:56AM

Or.... you could always upgrade your Mac to a PC!

bushmaster8 · February 10, 2006 2:02AM

<< Or.... you could always upgrade your Mac to a PC!

>>

...and have to deal with firewalls, spy ware, virus's etc!

mozin · February 10, 2006 2:09AM

On my Mac, I copied into a Word document and labeled it morganprices.iqy

When I tried to open this document with Excel, it was not recognized, so would not open. Anyone have more suggestions?

Man · February 10, 2006 2:11AM

I don't have Excel on my PC and it doesn't work.

dbldie55 · February 10, 2006 2:17AM

<< On my Mac, I copied into a Word document and labeled it morganprices.iqy

When I tried to open this document with Excel, it was not recognized, so would not open. Anyone have more suggestions? >>

You need to use a text editor.

Works great. would be easy to use with a macro to format this to work for other things. I used to download all the prices weekly and store them in a database. For my series the prices never changed.

cartwheel · February 10, 2006 2:33AM

Very cool! Thanks for the heads-up.

cartwheel

nolimitz · February 10, 2006 2:37AM

just tried it with " http://www.pcgs.com/prices/frame.chtml?type=date&filename=sba_dollar_mod"
Any ideas why it does not pull the PR SBA or any of the SAC's? Thanks

Edit: never mind it works!

lava · February 10, 2006 2:40AM

sounds cool -- I'll try it this weekend -- thanks for sharing

vega1 · February 10, 2006 2:41AM

That is very cool!

Thanks

relayer · February 10, 2006 2:55AM

I always liked your dad

mozin · February 10, 2006 3:02AM

<< 

<< On my Mac, I copied into a Word document and labeled it morganprices.iqy

When I tried to open this document with Excel, it was not recognized, so would not open. Anyone have more suggestions? >>

You need to use a text editor.

Works great. would be easy to use with a macro to format this to work for other things. I used to download all the prices weekly and store them in a database. For my series the prices never changed. >>

What are some examples of a "text editor"? I must have one somewhere on my machine.

dbldie55 · February 10, 2006 3:06AM

I used vi on my linux box to create the file.

nolimitz · February 10, 2006 3:19AM

What are some examples of a "text editor"? I must have one somewhere on my machine.

The missing test editor on Mac OS
or
free?

Know nothing of either of these. try google

BonPecheur · February 10, 2006 3:32AM

Cool ! Works good , Thanks for sharing

Dukie101 · February 10, 2006 3:44AM

Cool ! Works good , Thanks for sharing

Me too----THANKS

flaminio · February 10, 2006 3:55AM

Works sweet. Props to your dad.

For Windows users without Excel, you might be able to use Microsoft's free Excel Viewer. I haven't tried it, but it's worth a shot.

Rampage · February 10, 2006 5:26AM

It works quite well. Thank you for posting it, and thank you to you father for writing it.

Mercury · February 10, 2006 6:14AM

Cool, Thanks for sharing.

Vet · February 10, 2006 6:18AM

BECOKA · February 10, 2006 6:22AM

Fantastic. Works great.

jom · February 10, 2006 6:55AM

Hey...that's impressive! I've always wanted to try that method for getting stock quotes and stuff. Good job!

jom

RonMan · February 11, 2006 4:08AM

OK, why don't this work for me..

I've tried three times now and no luck.

I'm using XP Pro but an older version of Excel...Office 97.

Any ideas?

Ron

VamGuy · February 11, 2006 6:12AM

<< OK, why don't this work for me..

I've tried three times now and no luck.

I'm using XP Pro but an older version of Excel...Office 97.

Any ideas?

Ron >>

Ditto.

jdsinva · February 11, 2006 8:22AM

It may be this feature is not available in older versions of Excel. I have 2002 at home and 2003 at work and both have this feature.

See if you have this menu option on Excel:

If you have this option, just paste in the URL of the page you want to import, click on the arrows of the portions of the page you wish to import and click the import button. You can also save the query for later.

If you don't have these options in your Excel, you are out of luck until you update to a newer version, sorry.

robertpr · February 11, 2006 9:21AM

<< On my Mac, I copied into a Word document and labeled it morganprices.iqy

When I tried to open this document with Excel, it was not recognized, so would not open. Anyone have more suggestions? >>

When saving the file in MS Word, choose the file type "plain text" or "ascii text". Since the extension does not necessarily dictate the file type on a Mac, you may have to drag the icon of the file you just saved over the icon for MS Excel to get excel to open the file. I think you can also option-click on the file you saved to associate it with MS Excel but it's been a while since I've used a Mac so I forget.

OffMetal · February 11, 2006 9:38AM

<< My father wrote this very simple Excel inquiry that goes out and grabs what is on PCGS' site and creates an excel spreadsheet of it. I use it to keep track of the PCGS value of my collection. It is setup for Morgan Dollar's right now, but could easily be modified for any type of coin. I am not an excel expert but the script would be a good starting point for an inventory sheet of all coins. It would need a few more macros to make the raw data more useful.

Just copy the below (between but not including the stars) into notepad and save as "morganprices.iqy". Then double click it. It will open up an excel spreadsheet and load web site contents.

*************
WEB
1
http://www.pcgs.com/prices/frame.chtml?type=date&filename=morgan_regular

Selection=12
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
************* >>

I think that you can modify which series you want.

Just change the web address

mozin · February 11, 2006 9:42AM

<< 

<< On my Mac, I copied into a Word document and labeled it morganprices.iqy

When I tried to open this document with Excel, it was not recognized, so would not open. Anyone have more suggestions? >>

When saving the file in MS Word, choose the file type "plain text" or "ascii text". Since the extension does not necessarily dictate the file type on a Mac, you may have to drag the icon of the file you just saved over the icon for MS Excel to get excel to open the file. I think you can also option-click on the file you saved to associate it with MS Excel but it's been a while since I've used a Mac so I forget. >>

Thanks for the info.

I tried every which way along the lines of what you said. No luck. My 10.1.6 version of Excel that came with Office does not have the "import data" feature. I give up.

I think I will order a new HD TV and forget about coins.

anablep · February 11, 2006 12:52PM

Very cool!

Thank you!

I love this place. Always learning something new.

lava · February 11, 2006 2:17PM

I had no idea what I was doing but I followed your instructions and it works.

Now, would someone please tell me why I might want the price list in excell as opposed to just a printout of the prices?

numonebuyer · February 11, 2006 2:29PM

You must use a PC or a Mac with Virtual PC installed.

robertpr · February 12, 2006 12:09AM

<< I had no idea what I was doing but I followed your instructions and it works.

Now, would someone please tell me why I might want the price list in excell as opposed to just a printout of the prices? >>

1- You could import the sheet every month and put each month onto a different tab in excel, then graph the changes over time.

2- Excel sheets print much more nicely than web pages, and you can format them however you like for even better printing.

3- Excel sheets import and view more nicely on PDA's should you wish to take one to a show, etc. On the other hand, you could import, say, online greysheet prices and that would be better to take to a show.

4- Good for dialup types who are not always connected to the internet. You know, all five of them that are left.

NumisMe · February 12, 2006 1:19AM

Thanks for the info!

I have used this before, but I am hoping that your dad can still help me.

What I need to know is, how do you get to the data that is behind a login, such as the PCGS POP reports.
Of course you would have to be a member, and have a login and password already setup, but how do you get that information into the query so that you can access the data behind it.

I have been trying to figure this out for quite a while, and it would fantastic to solve the problem once and for all. It would add the finishing touch to my spreadsheet, and I know of at least a dozen others who would be very happy with it as well.

Hoping you (or your dad) can help.

mrearlygold · February 12, 2006 1:30AM

fcloud · February 12, 2006 1:47AM

Thanks, it was a simple change to mercury_dimes!

Oney · February 12, 2006 3:34AM

I think I have a solution for the pop report. PM me for answer....... not trying to keep a secret, but ......

slipgate · February 12, 2006 9:14PM

I use this to keep track of the PCGS total value of my collection. I have a spreadsheet that lists the coins that I own and the grade. I link the "current" value field to the spreadsheet created by the excel inquiry. When I want to update the current value, I simply run the query and save it as "current morgan value.xls" (always use the same name). When I open the "morgan inventory.xls" spreadsheet, it automatically updates the values from the "current morgan value.xls" spreadsheet and viola, I now have a current PCGS value of my collection.

This is somewhat simplistic in that if PCGS changes the format or adds a coin to the "official" collection, all of my value spreadsheets will need to be updated with the new cell location.

I'll see about adding restricted areas of the PCGS site.

JDelage · February 13, 2006 12:57AM

What I would like is an Excel sheet to import the Pinacle realized auction prices...

Useful Excel Files for coin inventories

Comments

Leave a Comment