get the model

While I tried my best to make the model as user-friendly as possible, I admit I'm not very good when it comes to intuitive visual design. The dashboard may be overwhelming, so I wanted to take some time and walk through how to use it.

The User Dashboard is where you'll likely be spending the vast majority of your time in the model. This is where you answer a few questions for the model to use as inputs to its profit calculations, review the output of the model, and analyze some of the trended historical data displayed in the charts. Here's what the top of the User Dashboard looks like right now:

Alright, that may look like a mess, and you're probably right. So lets tackle this piece by piece.

The very top of the dashboard has a welcome message, an option to change time zones, and a summary table of the profit calculations based on the current User Inputs below.

When you first download a version of the model, you should always change the timezone to your local one, that way all time-based data series will align with your perspective as well.

The profit summary table isn't meant to be interacted with, it simply puts in one place all the primary profit metrics for each event in Istan, making it easier to tell at a glance how profitable your farming habits are. For each highlighted section, the first number is the profit in coins (essentially the total number of copper coins it's worth), with the more familiar GW2 currency format below it.

This the most involved question that you need to answer. During my research, I came up with several options for drop rates that you can choose between, because I wanted to give users a bit of agency in deciding precisely how they want the model to calculate profits. Each option is broken into its own drop-down menu, and the lengthy notes to the right of the question explains much of what each option means.

I've left the drop-downs pre-selected to the options that I consider standard for my personal use, but that might not be suitable for you, so please take the time to review these options and select whichever ones are right for you. And if you have a suggestion or request for additional options, let me know through the Suggestions / Bug Report form.

After Question 5, the model has all the information it needs from you to calculate expected profits using up-to-date TP prices!

The next section summarizes all the primary calculations for each event, detailing the resources and items accrued from each part of the event, and totals up the net profit that is viewable at the top of the Dashboard as well.

Each table is calculated for Palawadan and Great Hall separately, but functionally they work the same, so I'll stick to explaining the Palawadan tables for now and you can apply the same knowledge to the Great Hall tables.

The first output table aggregates the total estimated resources you get in Volatile Magic (VM) and Kralkatite Ore (KO). You can see the vast majority of VM drops from the Supply Stashes you open, and in this example enough VM dropped during Palawadan to purchase 12 material shipments. KO drops at a much slower rate from stashes, and it's worth noting that the 15 KO you see from Events all comes from the large chest that drops when you kill Iberu in the Arena, If you don't participate in killing Iberu, you won't get that KO.

The second output table aggregates the total Unidentified Gear you get, by rarity. Again, most of this will come from Supply Stashes, but you'll also see that Palawan Phylacteries actually supply the most Green and Yellow Unidentified Gear, which make them valuable bags to collect when farming the Palawadan raid.

The third table at the bottom links to the API and back-end calculations to determine what the estimated profits are from salvaging or selling the gear. The Salvage Profit line takes into account your Magic Find level, since that helps determine the rarity of gear drops you get. The Sell Profit line is a straight link to the current pricing data for those Unidentified Gear on the TP. Below those price lines, the model tells you for each rarity of Unidentified Gear, and considering your specified Magic Find level, whether you should open and salvage the gear or sell it straight on the TP. It then calculates the total net profit (in coins) from each of those pieces of gear based on the recommendation it makes.

The final tables for the Model Output calculate the net profit you can expect from converting your VM and KO into material shipments. It links to back-end calculations that focus on the net profit from each kind of shipment, recommends which kind of shipment to buy in the green highlighted cell (Leather or Trophy as they're the most profitable), and estimates the total net profit you get from those resources.

The last two yellow highlighted tables on the right sum up all the profit sources from the Palawadan raid and calculate the absolute profit gained in the event's 35 minute run-time, and what that profit looks like prorated to a full hour.

The rest of the User Dashboard is filled with several charts that display key metrics such as net profit of material shipments, Unidentified Gear, and the main Istan meta-events, all trended over time. For these charts, you can change the time scale by entering a number between 1 and 21 in the appropriate yellow highlighted cells to the right of the charts, with the numbers representing the number of days you want trended in the chart. The charts have a resolution of 2 hour periods.

There are three other graphs that display the current calculated drop rates for Unidentified Gear of all rarities. These will update whenever new sample data is added to the appropriate worksheets elsewhere in the workbook, and give a helpful visual look at the drop rate you should expect at various Magic Find levels.

Each chart has a lengthy "notes" section to the right of it, and I'd encourage you to review that to help understand the charts better as well.

That's it for the User Dashboard! Next we'll cover how to update the API calls.

There are numerous data tables in the back-end of the workbook which pull current and historical pricing data from APIs and update roughly once per hour on their own. However, occasionally they won't update on their own, or you'll want to manually refresh them to check for the latest data. Here's how to use the "UPDATE API TABLES" sheet to do just that.

There are detailed instructions you can follow on the sheet itself, but to give a brief overview here, each API call is a formula on a back-end sheet that links to a yellow-highlighted cell on this sheet with the letters "api" in it. To force an API call to refresh, you need to break the formula by deleting the contents of the yellow "api" cell, wait for the associated "Table Status" cell to show "#ERROR", then fill the cell in again with the lower-case letters "api". That will reset the formula, and start another API call, updating it to the latest information.

Please note that the Item Price Trend tables have two ways of refreshing:

• You can delete the contents of the top-left "api" cell then type "api" back into it. Every cell next to the tables below is linked to that primary cell, so they will all break and update at the same time.
• If you want to just update one of the cells, delete the contents of that cell, but then replace it with a cell reference to the primary cell above. You can copy one of the other "api" cells for a table above and below it and do a special "paste formulas" to accomplish that too.

The Live Item Prices table is much more straight forward as it's only one table and you can just delete the text and type it in again to refresh the API call.

That's it for updating the APIs, it's pretty simple! Finally I'll address how to understand the "Drop Rate" data sheets, and how you can update them yourself to add in your own research that affects the calculated drop rates in the model.

The core profit calculations of the model rely on data collected both by the community and during my own research. Above is an example of the 'Leather Shipment Drop Rate" sheet, where you can see the format of the data table, a few calculated columns, and more information to the right of the table detailing the expected drop rate patterns of that item.

If you have your own research you would like to add to your copy of model, or happened across research a community member posted online that would be valuable, this is where you can input it.

I always want to credit the work of others, so the names of contributors and links to where they posted the data are provided for each line item. From there, data input is as simple as filling out the number of shipments opened, and how many items were received.

The last two columns however, "Total Units" and "Shipment Align Check", are auto-calculated columns based on the user's data entry, and serve to confirm the data entry meets expectations based on the drop rate rules for that item. All the items with drop rate sheets (e.g. Unidentified Gear, material shipments, Palawan Phylacteries, Supply Stashes) have rules for what kind of items can drop and how many of them can drop. These rules are detailed for your reference in the red highlighted notes to the right of the drop rate tables.

For Leather Shipments, the rule is pretty simple: each shipment drops 10 "units", and each unit can be a 10-stack of any tier leather materials. The auto-calculated columns confirm that the data you've entered aligns with those rules, and if so, returns "TRUE" in the "Shipment Align Column" and allows that data to be counted in the purple-highlighted "Drop Rates Per Unit" line at the top of the table.

However, if for example you enter that you received 95 Thick Leather Sections from however many Leather Shipments, the formulas will know that's not possible because materials only drop in stacks of 10. If the formulas return "FALSE", the data will not be included in the calculated drop rates.

I keep saying "calculated drop rates" because there are two drop rate lines at the top of the table. The purple is calculated directly from the raw data input below. The yellow are static numbers that I generated after taking a look at the patterns and considering what kinds of easy-to-use whole numbers would a developer actually use when programming drop rates like this. The yellow essentially adds a small human element to estimating the true drop rates. This is important to note because you have the choice in Question 5 on the User Dashboard of which drop rates you'd like to use, Calculated or Estimated (purple or yellow). Any data you add here will only affect the purple Calculated drop rates.

Finally, there are some columns in the drop rate sheets that are absolutely required for the model to use the data in its calculations, and others that are not. The section below highlights those different ones on each drop rate sheet.

If you would like your own data to help the community at large, you can submit it to me for inclusion in future releases of the model. Head over to the Data Submissions section of the site and fill out the appropriate form to have your data sent to me for verification. All sample helps, and I'll credit you on the drop rate sheets as well!

• Get the Model: How to Get It
• Get the Model: How to Use It
• Get the Model: Version History
• Get the Model: Special Thanks
• Get the Model: Suggestions / Bugs