• Play
  • About
  • News
  • Forums
  • Yppedia
  • Help
Welcome Guest   | Login
  Index  | Recent Threads  | Register  | Search  | Help  | RSS feeds  | View Unanswered Threads  
  Search  


Quick Go »
Thread Status: Normal
Total posts in this thread: 373
Posts: 373   Pages: 13   [ First Page | Previous Page | 4 5 6 7 8 9 10 11 12 13 | Next Page | Last Page]
[Add To My Favorites] [Watch this Thread] [Post new Thread]
Author
Previous Thread This topic has been viewed 93082 times and has 372 replies Next Thread
Marrt7

Member's Avatar


Joined: Jul 20, 2008
Posts: 26
Status: Offline
Re: The Shop Tool Reply to this Post
Reply with Quote

 
NEW VERSION 8.0 BETA NOW AVAILABLE at: http://yppshop.php0h.com/

I have completed the Beta for a new release 8.0 of the shop tool, adding the new oceans, colors and products, completing German and adding Spanish, making a bunch of enhancements, and fixing a number of bugs. The change log has been updated on the YPPedia page, and I will now update the main Shoptool page. Everything is done except some translations. I've been testing and it works great.

Please submit comments and bugs for the Beta by October 26. I plan to release the final 8.0 version by Halloween.

 
Bug, v8.0 Beta

Apothecary sheet
"Upper Limit" formulas (row 172) look at rows 140-171. They should use 99-171.

Great catch! That is actually left over from 7.1. I'll include the fix in 8.0.

I also added Upper Limit and Maximum Stock to the Realtor sheet, which was missing them, fixed the "Recipes" heading that was N/A, and hid the heading text that should have been hidden on that sheet. This looks like it is correct in the rest of the sheets.

I am going back over this forum, as there seem to be bugs reported but not fixed in 7.1 Beta, such as the Carven Chair formula problem (perhaps they were to be fixed in 7.2, but it never came out).

You may also PM me with any other problems if you prefer, or to let me know you are trying out the Beta.
----------------------------------------
-- Marrt
----------------------------------------
[Edit 4 times, last edit by Marrt7 at Oct 5, 2009 8:48:51 PM]
[Oct 4, 2009 5:51:32 PM] Show Printable Version of Post        Send Private Message    http://yppshop.php0h.com/ [Link]  Go to top 
mulsker

Member's Avatar


Joined: Apr 8, 2005
Posts: 13
Status: Offline
Re: The Shop Tool Reply to this Post
Reply with Quote

would be nice if you could add a reserve labour box beside the labour cost. and mix it together with the total poe so it wont look like you lost 500 poe or somthing like that.

you also got 17 uplinks to the internet. it takes a bit longer to update. not sure if you made more changes so you need them but i think you just made a copy - paste. made that mistake myself :P
----------------------------------------
[Edit 1 times, last edit by mulsker at Oct 7, 2009 12:30:34 PM]
[Oct 7, 2009 12:25:18 PM] Show Printable Version of Post        Send Private Message [Link]  Go to top 
Marrt7

Member's Avatar


Joined: Jul 20, 2008
Posts: 26
Status: Offline
Re: The Shop Tool Reply to this Post
Reply with Quote

 
you also got 17 uplinks to the internet. it takes a bit longer to update. not sure if you made more changes so you need them but i think you just made a copy - paste. made that mistake myself :P

There are now 10 oceans, 8 of which have doubloons, so it should be 19 downloads with the version check.

If you don't want to wait the 12 seconds (on Roadrunner) every time you bring the tool up, delete the tabs of oceans you don't use. I suggest leaving a 2nd (subscriber) ocean, so that way if you ever buy a shop on a different ocean, you can copy that ocean's tab from the downloaded file between the 2 remaining ocean tabs (rather than starting over from a blank download).

This is covered in the instructions, but it may not have been there yet when you downloaded the Beta.
----------------------------------------
-- Marrt
----------------------------------------
[Edit 1 times, last edit by Marrt7 at Oct 9, 2009 6:52:45 AM]
[Oct 7, 2009 6:41:29 PM] Show Printable Version of Post        Send Private Message    http://yppshop.php0h.com/ [Link]  Go to top 
Marrt7

Member's Avatar


Joined: Jul 20, 2008
Posts: 26
Status: Offline
Re: The Shop Tool Reply to this Post
Reply with Quote

I've found a few more bugs myself:
1. The new macro in $ Analysis got booched by a last-minute change. It works fine if you unprotect the sheet. The part that didn't happen if you got the error was to copy the shop totals from V6:AU6 to V4:AU4. If you already pushed the button, got the message, and went on, you can re-create the correct numbers in W4:AU4 by summing the weekly profits by shop from below (rows 49-250 in each column).

2. The rent calc for Realtor wasn't fully implemented (not sure it makes much sense for a Realtor, anyway, but 0 rent or labor entered makes it moot, and it should match the other shops)

3. Bugs in $ Analysis Commodities, Pre-Mades, and Stock formulas.

I wish I had thought about the Advance Labor - Mulsker is correct, but there is a bit more to it -- it needs a column in $ Analysis, and I think a line in the Charts.

I'm preparing to make an interim release 8.0.1 Beta1, hopefully in a few days. I am trying to set up a macro to copy data from the initial 8.0 Beta, or from the 7.1 Beta, to make this easier for you guys. There is a catch - if you added rows or columns, it won't work. Also if you add sheets for extra shops they won't be migrated. I am hoping I can set this up so that after this release it will be able to migrate those as well. If you have renamed any tabs, you will have to rename them back.

I am also trying to set up better for adding sheets for extra shops, making it automatically pick up extra shops for all Analysis functions, not just J4-9 as at present. If anyone in the Beta (or seeing this before the end of October 2009) uses that feature, I'd like to know: should the Commodity prices reflect one of the shops (the first) that makes the commodity, or an average of them. The latter would be a bit more complex, as I think it would require me to add columns ahead of the Financial Data to keep track of everything.
----------------------------------------
-- Marrt
----------------------------------------
[Edit 2 times, last edit by Marrt7 at Oct 9, 2009 5:44:18 PM]
[Oct 9, 2009 6:52:08 AM] Show Printable Version of Post        Send Private Message    http://yppshop.php0h.com/ [Link]  Go to top 
Marrt7

Member's Avatar


Joined: Jul 20, 2008
Posts: 26
Status: Offline
Re: The Shop Tool Reply to this Post
Reply with Quote

The automatic migration proved more time consuming than I expected to set up. I will just go ahead and include the fixes above with the 8.1 release early next week.

I also discovered that I could considerably reduce the size of the file by moving the Tailor Data Sheet onto the Tailor shop sheet. I was initially concerned that in order to clone the Tailor if one owned 2 tailor shops, one would need to clone both and then make changes to all the formulas to keep the rack pricer working for both. There were 3706 data cells in the pricer, including the Tailor Data. There were 80408 references between the 2 sheets, and with the new colors, language fixes, and Doubloon algorithm this went to 5106 cells and 110,808 cross-sheet references. When I moved these tables over to the Tailor sheet and removed the sheet references, the size of the file went down about 3 MB.
----------------------------------------
-- Marrt
[Oct 23, 2009 1:36:17 AM] Show Printable Version of Post        Send Private Message    http://yppshop.php0h.com/ [Link]  Go to top 
Marrt7

Member's Avatar


Joined: Jul 20, 2008
Posts: 26
Status: Offline
The Shop Tool 8.1 Reply to this Post
Reply with Quote

Shop Tool 8.1 is finally UP at http://yppshop.php0h.com/
(If you went to the site late 11/7 or early 11/8 and had difficulty, please try again; you might have to clear your browser cache as well.)

It provides Advance Labor support, and a number of bug fixes and enhancements (see http://yppedia.puzzlepirates.com/The_Shop_Tool/Changelog ).

If you enable macros, you should be able to bring over the data from your existing The Shop Tool spreadsheet using the "Migration Aid" tab. Open the new workbook first, then the one you want to migrate from; then flip back to the new workbook and press the button for the version you are migrating from. When the Realtor sheet displays, the migration is done.

If you have multiple sheets of the same shop type, or may have renamed any of the sheets, please see the notes on the Migration Aid sheet, and follow them carefully.

If a page that should have migrated comes up blank, make sure that the tabs have the same names in both workbooks, and the same name is in the Tab Names list in the new workbook, in a row for the same type that does not have #REF! in the Shop Names list.
----------------------------------------
-- Marrt
----------------------------------------
[Edit 6 times, last edit by Marrt7 at Nov 9, 2009 2:49:07 AM]
[Nov 8, 2009 1:50:02 AM] Show Printable Version of Post        Send Private Message    http://yppshop.php0h.com/ [Link]  Go to top 
Marrt7

Member's Avatar


Joined: Jul 20, 2008
Posts: 26
Status: Offline
Re: The Shop Tool Reply to this Post
Reply with Quote

If anyone still has the Bonus "Investment Tracker" and "Shop Records Parser" spreadsheets that Belgarion distributed with version 4.5 or so (see his July 1 2006 post in this thread), please PM me as I would love to see them. They could be a great help in the changes I am planning for the tool, as well as being helpful in their own right for current users.
----------------------------------------
-- Marrt
----------------------------------------
[Edit 1 times, last edit by Marrt7 at Nov 12, 2009 10:35:09 PM]
[Nov 8, 2009 6:14:14 PM] Show Printable Version of Post        Send Private Message    http://yppshop.php0h.com/ [Link]  Go to top 
Marrt7

Member's Avatar


Joined: Jul 20, 2008
Posts: 26
Status: Offline
Re: The Shop Tool Reply to this Post
Reply with Quote

Just posted a minor release to provide the new Scarecrow Furnisher Item. It is in the Outdoor section, although the game has been showing it in Seasonal as well in some oceans.

I also enhanced the migration to better handle buy prices, allowing users who may have unlinked as described in the instructions to migrate their buy prices or formulas from the shop sheets.

Migration now allows buy prices in both the Commodities sheet and the Shops to have formulas - I personally have been using formulas for some like =96*1.06, where 1.06 is an inexact approximation of the cost with fees I usually end up paying for bid tickets (pending the planned bid ticket support).

Buy Price formulas that are references are not migrated. Buy prices are either linked or unlinked in groups on each sheet, e.g. all herbs are either linked or unlinked. You can re-link by zeroing all entries in the group. For Basic Commodities, there are separate groups for the bought commodities and each made commodity.
----------------------------------------
-- Marrt
----------------------------------------
[Edit 2 times, last edit by Marrt7 at Nov 22, 2009 11:32:40 AM]
[Nov 19, 2009 4:02:06 AM] Show Printable Version of Post        Send Private Message    http://yppshop.php0h.com/ [Link]  Go to top 
Marrt7

Member's Avatar


Joined: Jul 20, 2008
Posts: 26
Status: Offline
Re: The Shop Tool Reply to this Post
Reply with Quote

Posted another minor release for the newer items. I've made checking for additional game changes a part of my routine for posting new releases!

Tailors - sorry about the rack pricing problem for Female only items - it is fixed now. There are 2 items with different formuae for Male and Female that you might have mispriced as a result - Diving Helmet and Captain's Jacket. You may notice that while most items that are common between Male and Female allow you to flip between Male and Female in the pricer, some now come up as N/A until you reselect them. The explanation is that these items (mostly shirts) are translated differently in Spanish, so I made them slightly different (an extra space at the end) to get the translations correct.
----------------------------------------
-- Marrt
----------------------------------------
[Edit 1 times, last edit by Marrt7 at Nov 22, 2009 12:17:59 AM]
[Nov 21, 2009 9:36:02 PM] Show Printable Version of Post        Send Private Message    http://yppshop.php0h.com/ [Link]  Go to top 
Marrt7

Member's Avatar


Joined: Jul 20, 2008
Posts: 26
Status: Offline
Re: The Shop Tool Reply to this Post
Reply with Quote

Posted a bugfix release to resolve a problem with migration of Labor Used in 8.1.1 and 8.1.2. It was removing the formulas when it should not have, leaving the values instead of the formulas that sum from the expanded table. If you migrate to version 8.1.3, it should fix the problem by only migrating the 3 Labor Used numbers if all non-formula numbers are integers (no decimals).
----------------------------------------
-- Marrt
[Nov 24, 2009 4:21:44 AM] Show Printable Version of Post        Send Private Message    http://yppshop.php0h.com/ [Link]  Go to top 
Marrt7

Member's Avatar


Joined: Jul 20, 2008
Posts: 26
Status: Offline
Re: The Shop Tool - Deleting Unused Ocean Tabs Reply to this Post
Reply with Quote

 
you also got 17 uplinks to the internet...

If you don't want to wait the 12 seconds (on Roadrunner) every time you bring the tool up, delete the tabs of oceans you don't use. I suggest leaving a 2nd (subscriber) ocean, so that way if you ever buy a shop on a different ocean, you can copy that ocean's tab from the downloaded file between the 2 remaining ocean tabs (rather than starting over from a blank download).

Leave the tabs for Midnight plus the Ocean(s) you use, or else be sure none of the shop sheets (including ones you don't use) selects an ocean whose tab you are deleting, or you will get #N/As on that sheet, Commodities, and $ Analysis.
----------------------------------------
-- Marrt
----------------------------------------
[Edit 1 times, last edit by Marrt7 at Dec 7, 2009 3:25:56 AM]
[Dec 7, 2009 3:22:14 AM] Show Printable Version of Post        Send Private Message    http://yppshop.php0h.com/ [Link]  Go to top 
Marrt7

Member's Avatar


Joined: Jul 20, 2008
Posts: 26
Status: Offline
Re: The Shop Tool Reply to this Post
Reply with Quote

Version 8.1.4 went up last night. It has the new formulas (2 days - a record) and items, plus improved Spanish translations (thanks, Verixx).

For anyone having difficulty reading my alleged German, Hibiskus is now reviewing the translations I added; I'll put up a new version when she is ready with them.
----------------------------------------
-- Marrt
[Dec 11, 2009 10:04:20 PM] Show Printable Version of Post        Send Private Message    http://yppshop.php0h.com/ [Link]  Go to top 
Sonnea

Member's Avatar


Joined: Nov 13, 2006
Posts: 169
Status: Offline
Re: The Shop Tool Reply to this Post
Reply with Quote

I'm ready and hope it will be a bit better now. The revised version is already on its way to Martt.

For the next weeks, I'm looking forward to work at the ShopTool Sheet at the german Yppedia. There's a lot of work to do.
----------------------------------------
Immer eine Handbreit Wasser unter dem Kiel, guten Wind und fette Beute
wünscht

Hibiskus
Prinzessin der Dark Shadows
Leitender Offizier der Lords of Darkness
----------------------------------------
[Edit 1 times, last edit by Sonnea at Dec 12, 2009 5:08:29 AM]
[Dec 12, 2009 5:07:48 AM] Show Printable Version of Post        Send Private Message    http://trixiehexe.de [Link]  Go to top 
Realjuan

Member's Avatar


Joined: Mar 14, 2007
Posts: 288
Status: Offline
Re: The Shop Tool Reply to this Post
Reply with Quote

Hi,
I just started using this spread sheet, and I think it is really good. Will help me a lot, but one think I can not find or there is none is:

You have minimum stock to many ANY minimum pieces of clothing, but I think that it could be even MORE useful to know the minimum to do ANY type of outfit.

Head, torso, Legs, Feet.

Shouldnt be to hard, to find the max amount in torsos + max in shoes + etc. *edit* the equation would be a little more complicated because you may want to allow all white, in which you have to add more)
Then have a total, and maybe have one for women and one for men?

Hope this helps you,
LatinBoy,
Sage
----------------------------------------
Latinboy
"Ten cuidado con el pirate del caribe, vamo' a ve' si tu tienes calibre" Daddy Yankee

"Gratitude is a duty which ought to be paid, but which none have a right to expect" Jean Jacque Rousseau
----------------------------------------
[Edit 2 times, last edit by Realjuan at Dec 15, 2009 2:10:00 PM]
[Dec 15, 2009 2:04:02 PM] Show Printable Version of Post        Send Private Message [Link]  Go to top 
blyndpew

Member's Avatar


Joined: May 18, 2005
Posts: 438
Status: Offline
Re: The Shop Tool Reply to this Post
Reply with Quote

Firstly, I am not an actual user of the shoppe tool, and I have not gone to the trouble of reading through all the threads to see if this question has already been answered. Feel free to ignore me.

A hearty of mine is a user of the tool, and uses a german localisation on her laptop. She was having problems with the auto-update, as if she did one, many of the calculated values broke.

We tracked it down to an issue that the taxes and prices retrieved from the web query were coming in as text, as the .-delimited values were not being recognised as numbers as her system uses ,-delimited.

I found a workaround was to put the following code in each ocean-sheet:

Private Sub Worksheet_Activate()
Application.DecimalSeparator = "."
Application.UseSystemSeparators = False
End Sub

Private Sub Worksheet_Deactivate()
Application.UseSystemSeparators = True
End Sub


Apologies if this issue has already been addressed.
----------------------------------------
Blyndpew/Albion
Not even an authorised spokesman for Me
[Dec 15, 2009 4:17:31 PM] Show Printable Version of Post        Send Private Message [Link]  Go to top 
Sonnea

Member's Avatar


Joined: Nov 13, 2006
Posts: 169
Status: Offline
Re: The Shop Tool Reply to this Post
Reply with Quote

 

A hearty of mine is a user of the tool, and uses a german localisation on her laptop. She was having problems with the auto-update, as if she did one, many of the calculated values broke.

We tracked it down to an issue that the taxes and prices retrieved from the web query were coming in as text, as the .-delimited values were not being recognised as numbers as her system uses ,-delimited.

I found a workaround was to put the following code in each ocean-sheet:

Private Sub Worksheet_Activate()
Application.DecimalSeparator = "."
Application.UseSystemSeparators = False
End Sub

Private Sub Worksheet_Deactivate()
Application.UseSystemSeparators = True
End Sub


Apologies if this issue has already been addressed.


Hmm, it would be very usefull if this code would work and would find a place in the Worksheet.

The *,* is used as the decimal seperator instead of *.*at all european locations except great britain, I think.
For that, I already worte an instruction how to change the localisation for the german YPPedia . But I'm afraid many people don't want to do system-changings every time they use the shop-tool..

So if Marrt could add this code into the worksheet it would be a big afford for the european users.
----------------------------------------
Immer eine Handbreit Wasser unter dem Kiel, guten Wind und fette Beute
wünscht

Hibiskus
Prinzessin der Dark Shadows
Leitender Offizier der Lords of Darkness
[Dec 16, 2009 9:29:05 AM] Show Printable Version of Post        Send Private Message    http://trixiehexe.de [Link]  Go to top 
Thanatosil



Joined: Sep 19, 2009
Posts: 1
Status: Offline

Re: The Shop Tool Reply to this Post
Reply with Quote

Found a bug...hope this is the right place to report it. On the weavery shop page, the dye prices are linked to the wrong cells on the commodities page. Lily of the valley links to the first dye color if memory serves.

Excellent tool though!!!

Thanatosil
[Dec 16, 2009 8:26:28 PM] Show Printable Version of Post        Send Private Message [Link]  Go to top 
Marrt7

Member's Avatar


Joined: Jul 20, 2008
Posts: 26
Status: Offline
Re: The Shop Tool Upper Limit and Minimum Stock Calculations Reply to this Post
Reply with Quote

 
I think that it could be even MORE useful to know the minimum to do ANY type of outfit.

Head, torso, Legs, Feet.

Shouldnt be to hard, to find the max amount in torsos + max in shoes + etc. *edit* the equation would be a little more complicated because you may want to allow all white, in which you have to add more)
Then have a total, and maybe have one for women and one for men?


Thanks for the suggestion. I will include this in the next version, coming in a few days. I will do at least an Outfit Upper Limit and an Outfit Minimum Stock. I don't really see why women and men would need to be shown separately - this is about keeping enough stock so someone can order an outfit. If a shopkeeper feels that women often order outfits while men generally order individual items, they can change the Outfit Upper Limit formulas to omit Male items if desired. Let me know if you disagree.

The main point is for users to have visibility on the minimum quantity needed to be able to order any item, so they can try to stock accordingly. However, for colors, the issue is more complex. To start with, for commodities with multiple colors you need to order at least the stock quantity of each color. The specific colors could be more or less than the generic (selectable) color for a commodity. The shopkeeper may also want to adjust the quantity on a different basis depending on what is popular or affordable.

I can see that users (for Tailor, Furnisher) may want to be able to support orders for primary plus secondary color being the same - I will look into providing secondary upper limits and minimum quantities using the max of the sum of the primary and secondary, which I think is what you were saying, but that is in addition to and separate from Outfit quantities. The quantities for the specific color commodities should also have a version that includes the primary and secondary. If an item has 5 primary, 4 secondary, 3 white, and 2 red, then the combined upper limit for white would be at least 5+4+3 = 12, and for red would be at least 5+4+2 = 11.

Do you agree that we need separate lines that reflect both numbers (3 and 12 for white, 2 and 11 for red)? There would be new lines for Combined Color Upper Limit and Combined Color Minimum Stock, which would be empty except in the Secondary color and Specific color commodity columns. Some users for some colors might want to have at least the quantity needed for just the secondary color (4) or the primary color (5) to be, for example, white, some might just want enough for the color-specific items (3), and some might want enough for an order for the primary, secondary and specific color to all be the same (12). So this would be a separate line for combined color limits and minimum stock. For tailor there could be an outfit version of these lines as well.

The instructions will need to be modified to explain how to use this information.

Also, IM, Shipyard, and Distillery currently don't calculate the stock levels for their colored commodities, and can be modified so that they do.

Finally, I notice that the Seasonal Furnisher items are not included in the formulas for Upper Limit, although it currently would not affect the quatntities if they were.
----------------------------------------
-- Marrt
----------------------------------------
[Edit 1 times, last edit by Marrt7 at Dec 17, 2009 4:15:25 AM]
[Dec 17, 2009 4:13:29 AM] Show Printable Version of Post        Send Private Message    http://yppshop.php0h.com/ [Link]  Go to top 
Williekillme

Member's Avatar


Joined: Nov 27, 2009
Posts: 23
Status: Offline
Re: The Shop Tool Reply to this Post
Reply with Quote

I guess this is the rite forum. I am trying to get use to the tool 1 thing I cant figure out is how to enter the Island and date opened. The other question is what data does the tool gather from site?

thanks for all the help
----------------------------------------
Williekillme on all oceans
Prince of MMW
[Dec 19, 2009 6:01:20 PM] Show Printable Version of Post        Send Private Message [Link]  Go to top 
kusje

Member's Avatar


Joined: Jun 10, 2005
Posts: 2101
Status: Offline
Re: The Shop Tool Reply to this Post
Reply with Quote

Prices for KB in the weavery are wrong. Not sure about the other commods there but I suspect KB might be wrong in the apoth as well.

*edit* Dye costs are all wrong in the weavery tab
----------------------------------------
Kneiss wrote: 
I have read so many philosophical books, i can't count. I have beaten my chesscomputer on the highest level several times. I have been classbest in math for several times.

----------------------------------------
[Edit 1 times, last edit by kusje at Dec 22, 2009 9:33:18 PM]
[Dec 22, 2009 9:31:57 PM] Show Printable Version of Post        Send Private Message [Link]  Go to top 
Forevever

Member's Avatar


Joined: Jul 19, 2004
Posts: 188
Status: Offline
Re: The Shop Tool Reply to this Post
Reply with Quote

 
Prices for KB in the weavery are wrong. Not sure about the other commods there but I suspect KB might be wrong in the apoth as well.

*edit* Dye costs are all wrong in the weavery tab


To correct the Dye Costs - highlight the grey box
At the top (in the white bar) it says Commodities!C37 for green - this refers to the line it pulls the information from on the commod sheet

should be the following
Red - Commodities!G19
Yellow - Commodities!G20
Blue - Commodities!G21
Green - Commodities!G22

Krakens Blood - Commodities!C56


You may need to unprotect the document before you can change this, I didn't try BEFORE unprotecting so I'm not sure - make a backup before you do this in case ye mek a boo boo <3

Edit - Blood is C56 not G56

And a great big giant THANK YOU to everyone who had a hand in finally updating this thing !!
----------------------------------------
Forevever and Foreverchaos
Cerulean and Meridian
----------------------------------------
[Edit 1 times, last edit by Forevever at Dec 26, 2009 2:44:09 PM]
[Dec 26, 2009 11:39:50 AM] Show Printable Version of Post        Send Private Message    forevever [Link]  Go to top 
aimela

Member's Avatar


Joined: Jun 16, 2007
Posts: 259
Status: Offline
Re: The Shop Tool Reply to this Post
Reply with Quote

I just downloaded 8.1.4 and proceeded to enter in info for my furnisher, however the value is erroring out for all the charged prices. I've made sure macros were enabled etc. Anyone else encountering this error?
----------------------------------------
Aimela
Sometimes we know we shouldn't and that's exactly why we do.
Avatar by the lovely Chibilover
[Jan 2, 2010 12:45:18 PM] Show Printable Version of Post        Send Private Message [Link]  Go to top 
Marrt7

Member's Avatar


Joined: Jul 20, 2008
Posts: 26
Status: Offline
Re: The Shop Tool Reply to this Post
Reply with Quote

The most likely cause for this would be a non-number or blank in one or more of the product or labor Use Cost fields. If you widen the column with the error, you would see #VALUE! if this is the problem. Non-numbers in the formulas could also be at fault, but would also affect the tax and cost/order.

If that isn't it, send me a PM and I can arrange to look at your spreadsheet.
----------------------------------------
-- Marrt
----------------------------------------
[Edit 1 times, last edit by Marrt7 at Jan 6, 2010 7:37:32 PM]
[Jan 6, 2010 7:33:13 PM] Show Printable Version of Post        Send Private Message    http://yppshop.php0h.com/ [Link]  Go to top 
mulsker

Member's Avatar


Joined: Apr 8, 2005
Posts: 13
Status: Offline
Re: The Shop Tool Reply to this Post
Reply with Quote

 
I just downloaded 8.1.4 and proceeded to enter in info for my furnisher, however the value is erroring out for all the charged prices. I've made sure macros were enabled etc. Anyone else encountering this error?


this is probably the problem you are having

 
The *,* is used as the decimal seperator instead of *.*at all european locations except great britain, I think.
For that, I already worte an instruction how to change the localisation for the german YPPedia . But I'm afraid many people don't want to do system-changings every time they use the shop-tool..

[Jan 13, 2010 11:46:52 AM] Show Printable Version of Post        Send Private Message [Link]  Go to top 
Marrt7

Member's Avatar


Joined: Jul 20, 2008
Posts: 26
Status: Offline
Re: The Shop Tool - European Separator Macro Solution Reply to this Post
Reply with Quote

 
A hearty of mine is a user of the tool, and uses a german localisation on her laptop. She was having problems with the auto-update, as if she did one, many of the calculated values broke.

We tracked it down to an issue that the taxes and prices retrieved from the web query were coming in as text, as the .-delimited values were not being recognised as numbers as her system uses ,-delimited.

I found a workaround was to put the following code in each ocean-sheet:

Thanks for the suggestion. Sorry for the long delay in getting back to work on this minor release. I expect to be able to get it done soon.

When I put the suggested code in my Spanish or German user IDs, it allows manual update to work. To do a complete manual update, for Doubloon oceans, you must update both the Commodities (select A1, right-click, Refresh data) and Doubloons (select D1 & refresh) tables separately in each ocean you use, as well as the Title Sheet to check for updates (unhide sheet, select B12 & refresh).

Adding the following to the ThisWorkbook VB macro object seems to allow the automatic update to work:

Private Sub Workbook_Open()
Application.DecimalSeparator = "."
Application.ThousandsSeparator = ","
Application.UseSystemSeparators = False
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.UseSystemSeparators = True
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.UseSystemSeparators = True
End Sub

This will cause the sheet that is open when you last save to appear initially when you open (and during the automatic update) with English-style separators, but to revert to the European separators as soon as you change a cell or go to a different sheet. I intend to set the thousands as well so we don't end up with a number like 1.000.00 showing.

I'll include the following macros in each ocean sheet object as well:

Private Sub Worksheet_Activate()
Application.DecimalSeparator = "."
Application.ThousandsSeparator = ","
Application.UseSystemSeparators = False
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Application.UseSystemSeparators = False
End Sub

That seems to make manual refresh work as well (assuming you are activating macros, of course). I did have it fail once in this setup, as before, but running the refresh again cleared it, and I couldn't get it to do it again. There aren't any thousands on these sheets right now that I know of, but black fine cloth is close.

I would encourage those of you bilinguals who are following this issue to try the above changes and let me know if there are any problems or further suggestions.
----------------------------------------
-- Marrt
----------------------------------------
[Edit 1 times, last edit by Marrt7 at Jan 29, 2010 1:44:11 PM]
[Jan 29, 2010 1:28:07 PM] Show Printable Version of Post        Send Private Message    http://yppshop.php0h.com/ [Link]  Go to top 
Marrt7

Member's Avatar


Joined: Jul 20, 2008
Posts: 26
Status: Offline
Re: The Shop Tool Reply to this Post
Reply with Quote

February has been a busy and interesting month for apocs, thanks to the Cupid ships, and I am afraid I haven't made much progress on the update.

I do want to let people know that if you are waiting for the new version to migrate from a version older than 8.1, don't, unless you want to migrate manually. To reduce the work of adding new fields and testing, I will be dropping the migration buttons for the earlier versions.

I have added 3 ad-hoc (user) columns to my version to help me with selling goods. There was a suggestion about adding columns as well - let me know if you have any suggestions along those lines.

The answer to the question above about date opened and island is that they are entered on the analysis page (sorry I didn't notice that one earlier). I think the Weaver questions were answered perfectly by the fix above - my Apoc sheet is working fine for KB.

Sorry about the delay in adding the new furnisher items - I encourage Furnishers to rename seasonal items that are no longer offered to new seasonal items and change the formulas accordingly. I also expect Shipyard owners to update the Limited Edition formula and title themselves as new ones come out. You may need to migrate these manually after such changes, but that should be a lot easier than migrating the whole workbook manually as in the older versions.
----------------------------------------
-- Marrt
[Feb 21, 2010 3:57:44 AM] Show Printable Version of Post        Send Private Message    http://yppshop.php0h.com/ [Link]  Go to top 
Luvessy

Member's Avatar


Joined: Dec 1, 2003
Posts: 2666
Status: Offline
Re: The Shop Tool Reply to this Post
Reply with Quote

I'm late to the party here.

How would I update the shipyard to account for new LE ships that use new minerals (gold nuggets, etc.)?
----------------------------------------
--Mistymate


(Avatar by Sancha.)
[Mar 9, 2010 8:38:36 PM] Show Printable Version of Post        Send Private Message [Link]  Go to top 
bey

Member's Avatar


Joined: Apr 26, 2005
Posts: 12
Status: Offline
Re: The Shop Tool Reply to this Post
Reply with Quote

is there a Mac compatible version of this? =/
----------------------------------------
Perxie of Viridian
SO of Kingdom of Heaven
Lady of Revelations
[Mar 9, 2010 9:46:53 PM] Show Printable Version of Post        Send Private Message [Link]  Go to top 
Marrt7

Member's Avatar


Joined: Jul 20, 2008
Posts: 26
Status: Offline
Re: The Shop Tool Reply to this Post
Reply with Quote

 
is there a Mac compatible version of this? =/


There are at least 2 references above, Oct 2008 and August 2007, to using this spreadsheet with Excel for Mac. If you have that software, you should be able to use this spreadsheet. I'm not sure if the problems with updating tax (and doubloon) data that are discussed in those pages are issues for Mac - there are other users that have similar problems on PCs, possibly due to browser security settings.

 
How would I update the shipyard to account for new LE ships that use new minerals (gold nuggets, etc.)?

1. Unprotect the shipyard sheet. (tools..protection..unprotect)
2. Change the name of the ship in both right-hand sections. These cells use a formula that picks up translations.
2a. If you don't use doubloons, you can just change the formula to the desired text. Select the cell and type the name in the data entry area at the top of the sheet.
2b. If your shop is on a doubloon ocean, you should select the new LE ship name from the end of the doubloon list on the ocean data page, and copy it from the data entry area at the top of the sheet. I recommend re-selecting cell A1 before switching away from the ocean page. Select cells AF45, AK16, and AK45, and paste the ship name into the data entry area of each. Verify that the doubloons are correctly appearing in cell BH45. Then hide columns AD thru AI again.
3. In the lower right section of the sheet, update the quantities in the line for the Limited Edition ship (cells AP45 thru BG45) to match the recipe in the game for the current limited edition ship. CAUTION: the cells for non-applicable products that appear blank actually have zeros in them - if you no longer need red paint, change it to a zero, not blank or null.
4. Protect the sheet again.

For German or Spanish users on doubloon oceans, put the English ship type from the ocean data sheet in Cell AF45, and either put your language in the other 2 cells as above, or unprotect the Language tab and change both the English and your language in the LE row there (or use a prototype row at the bottom of the sheet for the new LE ship).

(Users on doubloon could also follow the non-doubloon procedure 2a, and then just manually copy the doubloon number from the ocean sheet over the doubloon formula, if you think it is safe to assume that the number of doubloons is unlikely to change during the limited life of the ship - I think that is the case, but I run mostly on Midnight, so I can't say for sure.)
----------------------------------------
-- Marrt
----------------------------------------
[Edit 1 times, last edit by Marrt7 at Mar 14, 2010 5:48:59 PM]
[Mar 12, 2010 8:44:08 PM] Show Printable Version of Post        Send Private Message    http://yppshop.php0h.com/ [Link]  Go to top 
bel_pirates

Member's Avatar


Joined: Dec 15, 2003
Posts: 487
Status: Offline
Re: The Shop Tool Reply to this Post
Reply with Quote

I was cleaning out some old bookmarks and found this thread again and decided to peek in on the development. I must say, you've done a heck a job with the tool (along with the past developers and other contributors). The migration tool is one that I was hoping to get around to adding one day, but just didn't know how to do it.

Marrt7 wrote: 
If anyone still has the Bonus "Investment Tracker" and "Shop Records Parser" spreadsheets that Belgarion distributed with version 4.5 or so (see his July 1 2006 post in this thread), please PM me as I would love to see them. They could be a great help in the changes I am planning for the tool, as well as being helpful in their own right for current users.


Not sure if you're still interested in this or not, but...

I went digging for old files when I read this... I found several, including the Shop Log Parser and Investment Tracker, plus a few other personal sheets I had that you may or may not want. :)

The version of the ShopTool with the Log Parser integration is only found on the distillery sheet I believe. I was never able to get it fully going across the tool, which is why I took it out.

If you have any questions about the files, you can get in touch with me here.
----------------------------------------
Belgarion
Retired (for now)
The Shop Tool
[Apr 10, 2010 1:10:03 PM] Show Printable Version of Post        Send Private Message [Link]  Go to top 
Posts: 373   Pages: 13   [ First Page | Previous Page | 4 5 6 7 8 9 10 11 12 13 | Next Page | Last Page]
[Show Printable Version of Thread] [Post new Thread]

Puzzle Pirates™ © 2001-2016 Grey Havens, LLC All Rights Reserved.   Terms · Privacy · Affiliates