API tools faq
paste
GeorjCostanza

LTD Systems Search Instructions

GeorjCostanza
May 3rd, 2020 (edited)
1,730
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 71.12 KB | None | 0 0
raw download clone embed print report
  1. *** This is Version 2.451 of the LTD Systems Analysis Excel spreadsheet ***
  2.  
  3. June 29, 2020
  4.  
  5. Check here for updates:
  6. https://pastebin.com/wn58UUqe
  7.  
  8. The spreadsheet was designed on the latest version of Excel (V16) in 2020. The functions may not work on older versions, or Excel for Mac.
  9.  
  10.  
  11.  
  12. * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  13. UPDATE 2.41, June 13 2020
  14.  
  15. After the Fleet Carrier update the structure of the data received from EDDB changed, leading to a large overestimation of systems in Public Holiday and Pirate Attack.
  16.  
  17. For some reason the EDDN (and subsequently EDDB) is not syncing the 'system state' data with the 'controlling minor faction state' data, which is how we derived system states before the FC update.
  18.  
  19. I have found another method to calculate system states and hopefully this will be accurate going forward.
  20.  
  21. Please let me know if you encounter any bugs as I usually test an update for about 5 days, but in this case I've only tested for 24hrs.
  22.  
  23. Also, the Live Public Holiday and Pirate Attack worksheets won't produce accurate results until the state data structure is fixed, as EDDB is currently using old system state data for PH/PA. In reality there are only 70-100 systems in Public Holiday but EDDB is reporting over 600. Hopefully this will be resolved soon.
  24. * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  25. UPDATE 2.42, June 13 2020
  26.  
  27. A few CMDRs have requested I add back in separate analyses for pre-tick and post-tick data.
  28.  
  29. Running a pre-tick analysis after you download the EDDB JSON file (assuming you download the file before the next tick) will greatly reduce double-checking of systems that have already been checked by another CMDR.
  30.  
  31. Specifically, it excludes systems that have been checked after the most recent tick, which won't change until the next tick.
  32.  
  33. After the tick you'll need to run a 'post-tick' analysis, and this list of candidate systems will be much larger since all system states have the potential to change after the tick.
  34.  
  35. On the Action page, I've also added a 'Remove Small Population' button to remove systems with a population under 1,000,000. This is something I've been doing for a while manually, as small population systems rarely (if ever) have a high demand for LTDs.
  36.  
  37. * Please note, after the FC update there are 5 times the number of systems with 'pending expansion'. It used to be around 200, now it's closer to 900.
  38. As such, the 'Post-tick Full Analysis' will have around 800 systems to check. Therefore I recommend only running the Full Analysis using the 'Pre-tick' button (approx 150 systems), and only run the Core Analysis using the 'Post-tick' button. Hopefully the BGS will settle down soon.
  39.  
  40. Remember, after you remove the 'recovering' systems, and systems with a small population, the candidate lists become much more manageable.
  41.  
  42. As always, please let me know if you find any bugs, thanks!
  43. * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  44. UPDATE 2.43, June 15 2020 - BETA UPDATE - TICK REVISION
  45.  
  46. Using the knowledge that Expansion and Pending Expansion states are faction-wide (if one system controlled by a minor faction is in Expansion or Pending Expansion, then all their controlled systems will be in Expansion or Pending Expansion, respectively).
  47.  
  48. Therefore, in between the server tick and the release of the EDDB JSON files, if a CMDR had detected a system is in Pending Expansion then we know all the systems under the control of that faction will also be in Pending Expansion, at least until the next tick. Therefore we can exclude all of those systems controlled by that faction as it will be impossible for any of their systems to be in the X4 state (since X4 requires Expansion, not Pending Expansion).
  49.  
  50. I have added a button to remove systems in Confirmed Pending Expansion, in order to reduce search time. This will only apply to pre-tick analyses - in post-tick analyses (after the server tick) we don't know what changes in states have occurred so no systems will have confirmed Pending Expansion. This will make sense when you have been using the worksheet for a while, but in the meantime always click the 'Remove Pend Expan' button whenever you run an analysis.
  51.  
  52. Also note, the server tick time has recently changed, it is now at (approx) 09:00 GMT. For this reason, all previous version of this worksheet are no longer accurate. Please download this version, thank you.
  53. * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  54. UPDATE 2.44, June 28 2020 - BUG FIX
  55.  
  56. Fixed a minor bug in the Action sheet. Whenever a macro button was used to remove certain systems (i.e., remove low population, NPC faction, etc), if the table became empty after the systems were removed then Excel would fail to remove the filter that was used to identify the systems for removal.
  57.  
  58. So for example, if you pressed the 'Remove Low Population' button and the Action sheet became empty (all systems in the Action sheet had a population under 1 million), then the low population filter (next to the Population header) would not be cleared.
  59.  
  60. The error didn't actually affect any subsequent analyses, but the row numbers in the Action table would be highlighted in blue indicating a filter was present when it wasn't.
  61.  
  62. This bug has now been fixed via a hard reset of all filters in the Action table after each filter is applied.
  63. * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  64. UPDATE 2.45, June 29 2020 - SERVER TICK UPDATE
  65.  
  66. After the Fleet Carrier update the server tick time changed to 9am UTC. Over the past few weeks the server tick has become later and more variable, which messes with our commencement time to start checking systems for X4 states.
  67.  
  68. In this update I have set the server tick to 11am UTC/GMT, which should cover most server ticks.
  69.  
  70. Please keep an eye on
  71. https://elitebgs.app/tick
  72.  
  73. And if the tick time changes again please PM me on reddit (psychicEgg) so I can update the worksheet, thank you.
  74. * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  75. UPDATE 2.451, June 29 2020 - LIVE P/H AND P/A DATA AVAILABLE AGAIN
  76.  
  77. EDDB have fixed their live Public Holiday and Pirate Attack data tables, so I switched back on Live data import functions
  78. * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  79.  
  80.  
  81.  
  82. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  83. Introduction
  84. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  85.  
  86.  
  87. Hi CMDR, this tool will help you find stations that offer high prices for low-temperature diamonds (LTDs).
  88.  
  89. The LTD price is determined by the state of the controlling faction in a system.
  90.  
  91. Ideally we are looking for a combination of four states (aka X4 states):
  92.  
  93. Boom (or Investment) + Expansion + Civil Liberty + Public Holiday
  94.  
  95. This combination will activate an LTD price multiplier to around 1.6+ million/cr each.
  96.  
  97.  
  98. Slightly less ideal prices (around 1.1 million/cr can be found in systems that have Pirate Attack (instead of Public Holiday).
  99.  
  100. * Note, a system can't be in Boom and Investment at the same time, nor in Public Holiday and Pirate Attack at the same time.
  101.  
  102. Sometimes a 3-state pattern, such as Boom (or Investment) + Expansion + Public Holiday may offer prices equivalent to Pirate Attack (1.1 mil), but not always.
  103.  
  104.  
  105. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  106. Data used in the analyses
  107. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  108.  
  109.  
  110. Unfortunately we can't search for multiple system states on the GalMap, so we need to use other sources.
  111.  
  112. Specifically, we are going to use a large set of data that is graciously provided by EDDB.io (a HUGE thankyou to EDDB!).
  113.  
  114. EDDB data is collected by CMDRs on PC running applications that connect to the Elite Dangerous Data Network (EDDN). If you're on PC, then please, please, please run either EDMC or ED Discovery to contribute your travel data to EDDB/Inara.
  115.  
  116. - EDMC (https://github.com/Marginal/EDMarketConnector/wiki)
  117. - ED Discovery (https://github.com/EDDiscovery/EDDiscovery/wiki)
  118.  
  119. Ask for help on Reddit, Facebook or the official forums if you're not sure how to connect your account
  120. https://www.reddit.com/r/EliteDangerous/
  121. https://www.facebook.com/groups/elitedangerouscommunity/
  122. https://forums.frontier.co.uk/forums/elite-dangerous-newcomers-guides-tutorials/
  123.  
  124.  
  125. By the end of this tutorial, you'll be able to analyse the EDDB database for systems that are in (or likely to move into) the four states that multiply the LTD price.
  126.  
  127.  
  128.  
  129.  
  130. ==================================================
  131. ==================================================
  132.  
  133. These instructions are divided into two parts.
  134.  
  135. Part 1 describes how to set up your spreadsheet to use data from EDDB.io, and you'll only need to go through the setup once.
  136.  
  137. Part 2 describes how to run an analysis and check systems for high LTD prices.
  138.  
  139. ==================================================
  140. ==================================================
  141.  
  142.  
  143.  
  144. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-++-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  145.  
  146. Part 1: Setting up the spreadsheet: Connecting the spreadsheet to the EDDB data sources
  147.  
  148. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-++-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  149.  
  150.  
  151. Download the main spreadsheet from this link:
  152.  
  153. https://drive.google.com/file/d/1Jl7kq3XhL1_6fF0E40CwIItiZoTx4BiU/view?usp=sharing
  154.  
  155.  
  156. Before you open the spreadsheet, we need to grab three database files from EDDB.io
  157.  
  158. The spreadsheet uses three EDDB data sources:
  159. - systems_populated.json
  160. - stations.json
  161. - factions.json
  162.  
  163. A JSON (JavaScript Object Notation) file is simply a database file, similar to an Excel .xls file but in a different format.
  164.  
  165. We need our Excel spreadsheet to read data from these JSON files.
  166.  
  167. Firstly, download the three files from EDDB.io, and save them in a location on your PC that is easy to find.
  168.  
  169. https://eddb.io/api
  170.  
  171. On the main API page of EDDB.io you will see heaps of files available.
  172.  
  173. The files we want are:
  174. 1. systems_populated.json
  175. 2. stations.json
  176. 3. factions.json
  177.  
  178. Make sure you download the correct files, don't get the .jsonl files!
  179.  
  180.  
  181. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-++-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  182.  
  183. 1.1 Setting the JSON files location and loading them into Excel
  184.  
  185. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-++-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  186.  
  187.  
  188. Now that you have the files on your computer, we need to tell the spreadsheet where to find them.
  189.  
  190.  
  191. Load the spreadsheet LTD Systems Analysis V2.4.xlsm into Excel.
  192.  
  193. You will probably receive a popup notification, "This workbook contains links to one or more external sources that could be unsafe", or a 'Microsoft Excel Security Notice' like this (https://i.imgur.com/KMUuBeD.jpg)
  194.  
  195. This is for two reasons: First, the spreadsheet loads external data (the three EDDB files). And second, all the buttons in the spreadsheet activate macros that perform copy / paste / formula calculations / data clean up / and date & time refreshes.
  196.  
  197. There is no way to accomplish these tasks without macros, and while I can assure you they don't do anything beyond the functions of the spreadsheet, I have attached all the macros to an Appendix at the end of these instructions so you can inspect them yourself. If you're worried about the security of the macros, when you're inside the spreadsheet go to the 'View' menu and 'Macros' to view and match each macro to those listed in the Appendix.
  198.  
  199. Alternatively, you might receive a Security Warning because you will be loading external data (the three EDDB files). Select "Enable Content" to proceed, there is no risk, these files are directly from EDDB.
  200.  
  201. If you're super paranoid then I've included a version of the spreadsheet without the macros, however none of the buttons will function.
  202.  
  203.  
  204. Once you've loaded the spreadsheet, click on the Settings tab (although it should open to Settings), and read the instructions.
  205.  
  206.  
  207. Next we will tell Excel where to find the EDDB files you downloaded to your PC.
  208.  
  209. Scroll down to the green section entitled "EDDB JSON File Locations"
  210.  
  211. You have probably already downloaded the three EDDB files, but if not click the link and download them now.
  212.  
  213.  
  214. We use the three large blue buttons to tell Excel where to find each file.
  215.  
  216. Press the top blue button and locate 'stations.json' on your PC. When you have found the file, click on it, then press "Save".
  217.  
  218. Next, press the middle blue button and locate 'systems_populated.json', click it and then press 'Save'.
  219.  
  220. Next, press the lower blue button and locate 'factions.json', click it and then press 'Save'.
  221.  
  222.  
  223. The locations (directories) for these files should now appear to the left of these blue buttons.
  224.  
  225. Now would be a good time to save the Excel file so you never have to locate the files again (unless you save them to a different location in the future).
  226.  
  227.  
  228. Fantastic! That's the hard part done! Excel now knows where to look for the EDDB data.
  229.  
  230.  
  231. To load the EDDB data into the spreadsheet, click the red "Refresh All Data" button.
  232.  
  233. A "Queries & Connections" panel will open on the right side of the screen, and you will see each data query loading information from the EDDB files.
  234.  
  235. This takes about 4-5 minutes on my 2-year-old PC, and really depends on the speed of your CPU (and number of cores).
  236.  
  237. Please note: Excel will become unresponsive for a few minutes while it loads the 'Analysis' worksheet. It is performing over 250,000 calculations so please be patient. You can alt-TAB to work on something else while the data is loading, and a pop up notification will let you know when the data load is complete.
  238.  
  239.  
  240. After all the data loads it's probably a good idea to save the Excel file again. I save a new one each day after a data refresh, with the date in the title.
  241.  
  242.  
  243.  
  244. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-++-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  245.  
  246. Part 2: Analysis, and checking systems for high LTD prices
  247.  
  248. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-++-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  249.  
  250.  
  251. The two worksheets where you will spend the most time are the Analysis and Action worksheets (see the tabs on the bottom left).
  252.  
  253. Or if you run the automatic analyses from the Settings page, you will probably only spend time in the Action worksheet.
  254.  
  255.  
  256. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  257. Automatic Analysis
  258. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  259.  
  260.  
  261. Let's start by running an automatic analysis.
  262.  
  263. There are two analysis options on the Settings page: Core Analysis and Full Analysis.
  264.  
  265.  
  266. The Core Analysis runs the most critical analyses, and each is explained in the four CoreAnl tabs. Clicking the Core Analysis button grabs the systems from each of those four tabs and copies them to the Action worksheet.
  267.  
  268. The Full Analysis also grabs systems from the Extra Analysis tab (ExtraAnl) in addition to the Core Analysis tabs. The systems in the Extra Analysis tab have some of the target states but also have one or more states 'pending', and pending states become active states after a short period of time (which varies). Sometimes when a system moves from 2 states + a pending state into 3 active states it can also activate a Public Holiday or Pirate Attack. But this sequence is a little less common than the other analyses and adds a lot of systems to check, so we call this an Extra Analysis (though still worthwhile).
  269.  
  270.  
  271. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  272. Manual Analysis
  273. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  274.  
  275.  
  276. You can also run a manual (custom) analysis from the Analysis worksheet.
  277.  
  278. In the Analysis worksheet you can find which systems are in a particular combination of states.
  279.  
  280. You run an analysis by 'ordering' columns of data using the dropdown arrows at the top of the columns. Once you've done it a few times you'll get the hang of it.
  281.  
  282. Let's run through an example.
  283.  
  284. Go to the Analysis tab and start by looking at the Systems column.
  285.  
  286. When you first open the spreadsheet it will likely be sorted in alphabetical order.
  287.  
  288. And then to the right of the Systems you'll see a column for each faction state we're interested in, e.g., Boom, Investment, etc.
  289.  
  290. To the right of these States is a column called Target States. This column simply counts the number of simultaneous target states for a particular system. Because there are 4 possible states: Boom (or Investment), Expansion, Civil Liberty and Public Holiday (or Pirate Attack) then each system will be in 0,1,2,3, or 4 of those states.
  291.  
  292. * Note, as mentioned previously, a system can't be in Boom and Investment at the same time, and can't be in Public Holiday and Pirate Attack at the same time.
  293.  
  294. It would be very interesting for us to see if any systems currently have all 4 target states.
  295.  
  296.  
  297. Go to the top of column N [Target States], left click the small drop down arrow, and then click 'Sort largest to smallest'.
  298.  
  299. Hopefully you'll see some systems with '4' pop up.
  300.  
  301. Let's quickly check if those X4 systems have already been found by other CMDRs.
  302.  
  303. In a web browser, load up
  304. https://inara.cz/galaxy-commodity/144/
  305.  
  306. Are all of those X4 state systems already listed on the Inara 'LTD best sell' page?
  307.  
  308. If not, jump in your ship, load up EDMC or ED Discovery, and head over to any system not listed.
  309.  
  310.  
  311. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  312. Checking systems in-game
  313. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  314.  
  315.  
  316. When you arrive in the system you can check the current states of the system in the right-hand panel / Status tab.
  317.  
  318. On that Status page (in the game)
  319. - Click on the top faction
  320. - Then scroll down to the bottom of that faction page where you will see a summary of the states for that faction (which represents the system states)
  321.  
  322. If the system has 4 of the target states then you might like to visit a station (preferably a large one) and check the LTD prices
  323. (this is where it's important to be connected to EDMC or ED Discovery so these faction states and LTD prices will be automatically uploaded to Inara and EDDB)
  324.  
  325.  
  326. Please note, the states of each system can change slightly every day at the server 'tick' (refresh), which is usually about 12 hours after EDDB post their JSON files. For example, Public Holidays only last for a maximum of 2 days (2 server 'ticks').
  327.  
  328. To determine when the server tick happens in your local time, check this page (column Tick At (Local)):
  329.  
  330. https://elitebgs.app/tick
  331.  
  332. States (and therefore, LTD prices) ONLY change at the server tick. States and prices will not change between ticks. (If you're delivering LTDs and you're seeing a different price than what's listed on Inara, that's a different issue)
  333.  
  334. If all of the '4-state' systems are already listed on the Inara 'best sell' page then you can 'prospect' other systems that have 3 of the 4 states but are yet to be updated by other CMDRs (which means they might actually have all 4 states, they just haven't been checked yet).
  335.  
  336.  
  337.  
  338. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  339. Action Worksheet
  340. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  341.  
  342.  
  343. The Action worksheet is where you gather a list of candidate systems to check. If you run a Full Analysis there might be around 250 systems listed on the Action table, but don't be disheartened - there are many you won't need to check.
  344.  
  345. Systems with purple font are those priority systems with 4 target states, and are likely to have good LTD prices. They will usually already be listed on the EDDB/Inara LTD pages, but sometimes they haven't been discovered.
  346.  
  347.  
  348. 1. The first step is to type or paste your current location (system) into the Reference System box in the top-left of the worksheet. All distances will be calculated from your position.
  349.  
  350. Then press the purple 'Sort By Distance' button. This will change the order of the systems in the Action table, sorted by distance from your location. This sorting makes it efficient to jump around from system to system.
  351.  
  352.  
  353. 2. You may wish to remove systems 'In Recovery' by pressing the blue 'Remove Recovering' button. These are systems that recently had one of the four states but have now lost one of those states. Recovery is like a cooldown for states, so there's no chance you'll find an X4 state in a recovering system, so I usually exclude these systems from the candidate list. In the beginning you may wish to confirm this 'recovery theory' for yourself (and leave them in), and please do let me know if you notice any violations of the rule as we've only been using recovery exceptions for about a month.
  354.  
  355.  
  356. 3. If you're really short on time and want to focus your search, you may wish to remove systems that are controlled by non-player minor factions by pressing the blue 'Remove NPC Faction' button. I estimate 90-95% of X4 systems with high LTD prices are controlled by player factions, but very occasionally one will pop up with a non-player controlling faction. I don't usually exclude NPC controlling factions.
  357.  
  358.  
  359. 4. Before checking systems in-game (by visiting them in your ship), you should check on EDDB if that system has already been visited by another CMDR after the most recent tick. If you check the system on EDDB and observe it has already been checked after the last server tick, then you don't need to visit that system since its states can't change until the next tick.
  360.  
  361. That's a very important point.
  362.  
  363. It means that it's best to wait some hours after the tick before you start checking systems. Immediately after the tick we don't know the actual states of any systems (because every system can change). Therefore, if we started working our way through the candidate list immediately after the server tick, we would have to fly around to every system to check their states.
  364.  
  365. I usually start checking about 8 hours after the tick (conveniently it's when I wake up in my part of the world). And when I pre-check systems on EDDB I often notice 40-80% of candidate systems have already been checked by other CMDRs - either through focused checking like we're doing here, or just by their natural movements throughout the bubble. Remember, every CMDR running ED Discovery or EDMC automatically uploads their system data as they fly around, so many of our candidate systems will be checked by chance.
  366.  
  367.  
  368. So to run a pre-check on the candidate list, there is a red button at the top 'Batch Open Hyperlinks (10)'. Pressing this button will open the EDDB web pages for the first 10 systems on your candidate list.
  369.  
  370. Note: I've set a cooldown of 30 seconds on this button so as not to annoy EDDB with opening pages too quickly (one per 3 seconds should be ok).
  371.  
  372.  
  373. Before looking at the web pages, note the Hours and Minutes just above the 'Days Since Update' column. These are the hours and minutes since the last server tick. If a system has been checked within this range, we don't need to visit that system ourselves and can exclude it from the candidate list.
  374.  
  375. Say for example, the 'time since last tick' is 10 hours. When you look at the EDDB page for a system, you'll see along the top (blue) line for the minor faction, on the far right, there is an Update: time provided in hours (or days) from the present.
  376.  
  377. Therefore, if I look at a system on the EDDB web page and see Update: 4 hours ago, and the time since last tick is 10 hours, then someone else has already visited that system since the last tick, and I can exclude that system from my candidate list (but remember to check for X4 states on the EDDB page).
  378.  
  379. When looking at the EDDB page, you only need to check the states for the controlling faction (the top one with the highest % influence). Non-controlling minor factions never have X4 states (or if they do, I've never seen one).
  380.  
  381. Example pic
  382. https://i.imgur.com/PWDHmvP.jpg
  383.  
  384.  
  385.  
  386. If you don't need to check the system, you can go back to the Action table, put your selection box on that system name (or the row containing that system), and then press the 'Red Font' button. This will mark the system with a red font to remind you that you don't need to check that system.
  387.  
  388. However, if you look at the EDDB page and see that the Updated: time is greater than the hours since last tick, this means it hasn't been checked after the last tick, and might be in an X4 state without anyone knowing about it.
  389.  
  390. So in that case, you can go back to your Action table and mark that system with the 'Green Font' button to remind you to check that system in-game.
  391.  
  392. When you're actually doing this, I find the majority of my candidate systems have already been checked, so I don't go back and mark each one in red - I just mark those in green that need to be checked in-game. Saves a bit of time, but you'll get the hang of it.
  393.  
  394.  
  395. After you press the button to open the hyperlinks, you'll notice those links in the Action table are marked in khaki green. This is partly to let you know which systems you've already checked in EDDB, but also tells the 'open hyperlinks' button to skip those systems when you press it again.
  396.  
  397. I usually pre-check the whole list of candidate systems before heading out into space. Once you've done it a few times you can do it very quickly, and I find it a lot faster to pre-check before launch than doing them while flying around. But everyone has their own style.
  398.  
  399.  
  400.  
  401. 5. Let's say you've found some systems in EDDB that need to be checked in-game, and you've marked them with a green font on your candidate list. The system in green closest to the top of the list will be the system closest to your current location. So fire up your thrusters, raise your landing gear, and head over to the green font system to check it out.
  402.  
  403. When you get there (and please make sure you're connected to ED Discovery or EDMC so other CMDRs don't have to check the same system twice!), go into your right-hand panel / Status / top faction, and check their system states. 99 times out of 100 you won't find an X4 system.
  404.  
  405. So go back into your Action table, highlight the system that you have just travelled to, and then press the 'Next System' button.
  406.  
  407. The Next System button marks the selected system in red (so you know it doesn't need to be visited again), copies that system to the Reference System in the top-left (so all distances are calculated from your current location), and then sorts the system list by distance from your current location.
  408.  
  409. Now look at your candidate list, and fly to the next green system that needs to be checked.
  410.  
  411. And then do that over and over and over again :)
  412.  
  413. But when you do find an X4 system it feels like discovering gold! Hopefully that system will have a station with a large landing pad. Go ahead and dock, check the commodity market, and this will upload the LTD price to the Elite Dangerous Data Network. Then once you upload that LTD price to EDDB/Inara you'll see CMDRs flocking to your system like hungry seagulls chasing a chip! Congratulations, you've made a contribution to the community, and now you can rest :)
  414.  
  415.  
  416.  
  417. 6. As you check systems on EDDB or in-game, take note if that system is in Expansion. Expansion is a unique state because it's the only one that's faction-wide.
  418.  
  419. This means if you find one system in Expansion under the control of a particular minor faction, then all systems under their control will also be in Expansion. It also means that if you check a system on EDDB or in-game and it's NOT in Expansion, then all systems under that minor faction's control will NOT be in the X4 state (since the X4 state requires Expansion). Therefore, if we find one system without Expansion, we can exclude all others from the same minor faction on the candidate list.
  420.  
  421. When you find a system on your candidate list without Expansion (or it might have Pending Expansion or Recovering Expansion .. these also need to be excluded) then highlight the faction (or the current system) and press the yellow "Mark No Expansion" button. This will apply red font to all instances of that minor faction in your candidate list, which will remind you to exclude their systems from checking (since it's impossible they will have the X4 state).
  422.  
  423. You can also press the "Remove No Exp" button to delete all those systems with a minor faction marked in red, to declutter your Action table.
  424.  
  425.  
  426. 7. And that's essentially the way we check systems for high LTD prices. It might sound pretty straight forward but 3 months ago we didn't have a strategy at all, and everything you're reading here had to be discovered slowly over time.
  427.  
  428.  
  429.  
  430.  
  431. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  432. Live Public Holiday (and PirateA) Data
  433. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  434.  
  435. Most CMDRs aren't checking systems for X4 states. Therefore, many will roam around the bubble and accidentally uncover a Public Holiday (or Pirate Attack) system without ever knowing.
  436.  
  437. Therefore, it would be useful for us to have a way to check if any Public Holidays have been uncovered in systems outside our candidate list.
  438.  
  439. You can do this by clicking on the yellow LivePubH tab, and following the instructions on the page. It essentially involves copying and pasting the Public Holiday systems data from EDDB, and only takes about 20 seconds. I've found many X4 systems this way, so it's worth checking regularly.
  440.  
  441.  
  442.  
  443. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  444. Faction Analysis (experimental)
  445. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  446.  
  447. As you become familiar with the procedure, you'll notice there are a number of player factions that often appear in the candidate list. These player factions often have 10 to 20 systems under their control, and if they've created an X3 state (Boom+Exp+Civ Lib) it means they must be doing pretty well, and I call these factions 'hot factions'.
  448.  
  449. If you click on the purple Faction tab you'll see a list of 'hot factions' down the left side, and a Research table in the middle.
  450.  
  451. On the 'hot factions' list, click one of the factions you often see in the Action table and then press the Research button. This will display all the system controlled by that faction in the middle table.
  452.  
  453. And you'll probably notice some of those systems are marked in yellow because they haven't been visited for over 2 days.
  454.  
  455. It's my theory that those out-of-date systems controlled by a 'hot faction' may be hiding an X4 state. This does happen on occasion, so if you have some spare time it might be worthwhile checking the out-of-date systems for a few 'hot factions'.
  456.  
  457. Please let me know if you have any thoughts on how to narrow the focus further to particular factions that are more likely to be hiding an X4 state.
  458.  
  459.  
  460.  
  461. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  462. Updating the JSON files from EDDB
  463. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  464.  
  465. Each day you'll need to update the three JSON files from EDDB.io to keep the data current.
  466.  
  467. After downloading the files, remember to press the red "Refresh All Data" button on the Settings worksheet to load the new EDDB data into Excel.
  468.  
  469.  
  470.  
  471.  
  472. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  473. Extra Notes
  474. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  475.  
  476. The spreadsheet is set up to find LTD stations even in systems where the primary economy isn't a HIRTo economy (High-tech, Industrial, Refinery, Tourism). Originally I was only using HIRTo primary economies to locate candidate systems, and noticed I was missing high LTD sell prices in stations that had their own HIRTo economy status separate from the non-HIRTo primary economy of the system. In other words, sometimes a station's economy can be different to the system economy.
  477.  
  478. (HIRTo primary economies) plus (HIRTo stations in non-HIRTo primary economies) ≈ 14,160 systems
  479.  
  480. HIRTo primary economies ≈ 9,500 systems
  481.  
  482. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  483.  
  484. Sometimes when you find an X4 system and visit a station you'll look at the LTD price and be disappointed it's very low. What's going on there?
  485.  
  486. Firstly, the station must have a HIRTo economy - you can check that in-game or on Inara or EDDB.
  487.  
  488. Secondly, that station must be managed by the controlling faction (check it on EDDB). Sometimes there are many stations in a system, and only one or two are managed by the controlling faction. Stations that aren't managed by the controlling faction will always have low LTD prices.
  489.  
  490. There's also an occasional low price in an Extraction/refinery economy but I'm not sure why (most X4 Extraction/refinery stations will have high LTD prices).
  491.  
  492.  
  493.  
  494. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  495. Credits
  496. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  497.  
  498. I am truly grateful to my Excel guru, CMDR Sad Samurai (Johny_D_Doe on reddit) for catching and fixing a bug in the first version of the worksheet, and for teaching me how to manage power queries and table references.
  499.  
  500. These new versions of the spreadsheet wouldn't exist without his input, so a sincere thank you CMDR o7
  501.  
  502. Also, another huge thank you to EDDB for supplying the daily JSON files, without which none of this would be possible.
  503.  
  504.  
  505.  
  506. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  507. Conclusion
  508. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  509.  
  510. Thank you for taking the time to read through these instructions. If you found anything confusing please let me know.
  511.  
  512. You're welcome to contact me if you need any help, or would like a spreadsheet that displays different data.
  513.  
  514. Good hunting,
  515.  
  516. o7
  517.  
  518. Reddit: psychicEgg
  519. ED: CMDR GeorjCostanza
  520.  
  521. PS. There's lots of other things you can do with the data
  522. - go into Analysis and order the Systems by Days Since Update. Some LTD systems haven't been updated for a looooong time! If you visit them and update their info you might find an LTD surprise :)
  523. - go into the Systems tab and find 'populated' systems with zero population..
  524. - have fun treasure hunting, and let us know on reddit if you have some success
  525.  
  526.  
  527.  
  528.  
  529. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  530.  
  531. ============ Appendix: List of Macros ===============
  532.  
  533. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  534.  
  535.  
  536. Private Sub Workbook_Open()
  537.  
  538. 'hides all arrows except list 1 column 2
  539. Dim Lst As ListObject
  540. Dim c As Range
  541. Dim i As Integer
  542. 'Application.ScreenUpdating = False
  543. Set Lst = ActiveWorkbook.Sheets("Factions").ListObjects("FactionResearch")
  544. i = 1
  545. For Each c In Lst.HeaderRowRange
  546. Lst.Range.AutoFilter Field:=i, _
  547. VisibleDropDown:=False
  548. i = i + 1
  549. Next
  550. 'Application.ScreenUpdating = True
  551.  
  552. End Sub
  553.  
  554.  
  555. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  556. Range("J17").Calculate
  557.  
  558. ' Detect GMT(UTC) time
  559. Dim dt As Object, utc As Date
  560. Set dt = CreateObject("WbemScripting.SWbemDateTime")
  561. dt.SetVarDate Now
  562. utc = dt.GetVarDate(False)
  563.  
  564. 'strAddress = "Settings!B11"
  565. 'Range(strAddress).Value = utc
  566. Sheets("Settings").Range("B11") = utc
  567.  
  568. End Sub
  569.  
  570.  
  571. Private Sub Worksheet_Activate()
  572. Range("B6").Calculate
  573.  
  574. ' Detect GMT(UTC) time
  575. Dim dt As Object, utc As Date
  576. Set dt = CreateObject("WbemScripting.SWbemDateTime")
  577. dt.SetVarDate Now
  578. utc = dt.GetVarDate(False)
  579.  
  580. strAddress = "B11"
  581. Range(strAddress).Value = utc
  582. End Sub
  583.  
  584. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  585. Range("B6").Calculate
  586.  
  587. ' Detect GMT(UTC) time
  588. Dim dt As Object, utc As Date
  589. Set dt = CreateObject("WbemScripting.SWbemDateTime")
  590. dt.SetVarDate Now
  591. utc = dt.GetVarDate(False)
  592.  
  593. strAddress = "B11"
  594. Range(strAddress).Value = utc
  595.  
  596. End Sub
  597.  
  598.  
  599. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  600.  
  601. Set tbl = Sheets("LivePubH").ListObjects("PubHTable")
  602. strAddress = "H18"
  603.  
  604. If IsEmpty(Range("A21").Value) = True Then
  605. lastRowPubH = 0
  606. Range(strAddress).Value = lastRowPubH
  607.  
  608. Else
  609. lastRowPubH = tbl.Range.Rows.Count
  610. Range(strAddress).Value = lastRowPubH - 1
  611. End If
  612.  
  613. End Sub
  614.  
  615.  
  616. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  617.  
  618. Set tbl = Sheets("LivePirA").ListObjects("PirATable")
  619. strAddress = "H18"
  620.  
  621. If IsEmpty(Range("A21").Value) = True Then
  622. lastRowPirA = 0
  623. Range(strAddress).Value = lastRowPirA
  624.  
  625. Else
  626. lastRowPirA = tbl.Range.Rows.Count
  627. Range(strAddress).Value = lastRowPirA - 1
  628. End If
  629.  
  630. End Sub
  631.  
  632.  
  633.  
  634. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  635. On Error Resume Next
  636.  
  637. Dim cmdBtn As Shape
  638. Const iCOL_RESTR As Integer = 8
  639. Set cmdBtn = Me.Shapes("Button 1")
  640.  
  641. With ActiveSheet.ListObjects("Analysis")
  642.  
  643. cmdBtn.Left = Me.Range("Analysis[[Boom]]").Left
  644. cmdBtn.Top = ActiveWindow.VisibleRange.Top + 0.4 * ActiveWindow.VisibleRange.Height
  645.  
  646. End With
  647.  
  648. End Sub
  649.  
  650.  
  651. Sub Auto_Open()
  652.  
  653. Range("Settings!B6").Calculate
  654.  
  655. ' Detect GMT(UTC) time
  656. Dim dt As Object, utc As Date
  657. Set dt = CreateObject("WbemScripting.SWbemDateTime")
  658. dt.SetVarDate Now
  659. utc = dt.GetVarDate(False)
  660.  
  661. Sheets("Settings").Range("B11") = utc
  662.  
  663. End Sub
  664. Sub Redfont()
  665.  
  666. On Error Resume Next
  667.  
  668. Selection.End(xlToLeft).Select
  669.  
  670. With Selection.Font
  671. .Color = -16776961
  672. .TintAndShade = 0
  673. End With
  674.  
  675. End Sub
  676. Sub Greenfont()
  677.  
  678. On Error Resume Next
  679.  
  680. Selection.End(xlToLeft).Select
  681.  
  682. With Selection.Font
  683. .Color = -11753472
  684. .TintAndShade = 0
  685. End With
  686.  
  687. End Sub
  688. Sub ExportRngAsCSVFile()
  689.  
  690. On Error Resume Next
  691.  
  692. Dim CopyRng As Range, FileNm As String
  693. xTitleId = "LTD Systems Export"
  694. Set CopyRng = Application.Selection
  695. Set CopyRng = Application.InputBox("Range", xTitleId, CopyRng.Address, Type:=8)
  696.  
  697. FileNm = ActiveWorkbook.Path & "\" & xTitleId & Format(Date, " ddmmmyyyy") & ".csv"
  698.  
  699. Application.ScreenUpdating = False
  700.  
  701. Workbooks.Add (1) 'A new workbook with a worksheet
  702. With ActiveWorkbook
  703. CopyRng.Copy
  704. .Sheets(1).Range("A2").PasteSpecial Paste:=xlPasteValues
  705. .SaveAs Filename:=FileNm, FileFormat:=xlCSV
  706. .Close SaveChanges:=True
  707. End With
  708.  
  709. Application.ScreenUpdating = True
  710.  
  711. MsgBox "Data exported to current workbook directory"
  712.  
  713. End Sub
  714. Sub BatchOpenHyperLinks_SelectedRanges()
  715.  
  716. On Error Resume Next
  717.  
  718. Dim i As Integer
  719. Dim time1, time2
  720.  
  721. 'Haz hyperlinks?
  722. If IsEmpty(Range("B21").Value) = True Then Exit Sub
  723.  
  724. 'Cooldown
  725. Dim myshape As Shape: Set myshape = ThisWorkbook.Worksheets("Action").Shapes("HyperBatch")
  726. If myshape.TextFrame.Characters.Font.ColorIndex = 15 Then Exit Sub
  727. With myshape
  728. .TextFrame.Characters.Font.ColorIndex = 15 '---> Grey out button label
  729. End With
  730.  
  731. 'Select Range
  732. Range("B21").Select
  733. If IsEmpty(Range("B22").Value) = True Then
  734. Range("B21").Select
  735. Else
  736. Range(Selection, Selection.End(xlDown)).Select
  737. End If
  738.  
  739. For Each xCell In Selection
  740.  
  741. If i = 10 Then GoTo Cooldown
  742.  
  743. If xCell.Font.ColorIndex = 12 Then
  744. Else
  745. xCell.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Value
  746. xCell.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
  747. xCell.Font.ColorIndex = 12
  748. i = (i + 1)
  749. End If
  750. Next xCell
  751.  
  752. GoTo Cooldown
  753.  
  754. Cooldown:
  755.  
  756. ActiveSheet.ListObjects("ActionTable").Range.AutoFilter Field:=2, Criteria1 _
  757. :=RGB(128, 128, 0), Operator:=xlFilterFontColor
  758. Range("ActionTable[[#Headers],[Web Link]]").Select
  759. ActiveCell.Offset(1, 0).Range("A1").Select
  760. Selection.End(xlDown).Select
  761. ActiveCell.Offset(0, -1).Range("A1").Select
  762. ActiveSheet.ListObjects("ActionTable").Range.AutoFilter Field:=2
  763.  
  764. 'Wait 30 seconds
  765. time1 = Now
  766. time2 = Now + TimeValue("0:00:30")
  767. Do Until time1 >= time2
  768. DoEvents
  769. time1 = Now()
  770. Loop
  771.  
  772. With myshape
  773. .TextFrame.Characters.Font.ColorIndex = 2 '---> Restore button label
  774. End With
  775.  
  776. i = 0
  777.  
  778. End Sub
  779. Sub ResetHyper()
  780.  
  781. On Error Resume Next
  782.  
  783. Dim myshape As Shape: Set myshape = ThisWorkbook.Worksheets("Action").Shapes("HyperBatch")
  784. With myshape
  785. .TextFrame.Characters.Font.ColorIndex = 2 '---> Restore button label
  786. End With
  787.  
  788. Range("B21:B1048576").Select
  789. With Selection.Font
  790. .ColorIndex = 41
  791. .TintAndShade = 0
  792. End With
  793.  
  794. Range("A21").Select
  795.  
  796. End Sub
  797.  
  798.  
  799. Sub CopyToTable()
  800.  
  801. Selection.Copy
  802. Sheets("Action").Select
  803. Range("ActionTable[[#Headers],[Systems]]").Select
  804.  
  805. Set tbl = Sheets("Action").ListObjects("ActionTable")
  806. lastRow = tbl.Range.Rows.Count
  807.  
  808. If IsEmpty(Range("B21").Value) = True Then
  809. Selection.End(xlDown).Select
  810.  
  811. Else
  812. Range("A" & lastRow).Offset(20).Select
  813. End If
  814.  
  815. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  816. Range("A" & Rows.Count).End(xlUp).Offset(1).Select
  817. Application.CutCopyMode = False
  818.  
  819. End Sub
  820. Sub ResetTable()
  821.  
  822. On Error Resume Next
  823. Range("B21:B1048576").Select
  824. With Selection.Font
  825. .ColorIndex = 41
  826. .TintAndShade = 0
  827. End With
  828.  
  829. Range("F21:F1048576").Select
  830. With Selection.Font
  831. .ColorIndex = xlAutomatic
  832. .TintAndShade = 0
  833. End With
  834.  
  835. Range("A21:A1048576").Select
  836. With Selection.Font
  837. .ColorIndex = xlAutomatic
  838. .TintAndShade = 0
  839. End With
  840.  
  841. Application.CutCopyMode = False
  842. Selection.ClearContents
  843. ActiveSheet.Range("A21").Select
  844.  
  845. With ActiveSheet.ListObjects("ActionTable")
  846. .Range.AutoFilter Field:=1, Criteria1:="="
  847. .DataBodyRange.EntireRow.Delete
  848. .Range.AutoFilter Field:=1
  849. End With
  850.  
  851. End Sub
  852. Sub DelBlankRows()
  853.  
  854. On Error Resume Next
  855.  
  856. With ActiveSheet.ListObjects("ActionTable")
  857. .Range.AutoFilter Field:=1, Criteria1:="="
  858.  
  859. Set ws = Sheets("Action").ListObjects("ActionTable")
  860. Rowz = ws.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1
  861.  
  862. If Rowz >= 1 Then
  863. .DataBodyRange.EntireRow.Delete
  864. End If
  865. .Range.AutoFilter Field:=1
  866. End With
  867.  
  868. End Sub
  869.  
  870.  
  871. Sub NextSystem()
  872.  
  873. On Error Resume Next
  874.  
  875. Selection.End(xlToLeft).Select
  876.  
  877. With Selection.Font
  878. .Color = -16776961
  879. .TintAndShade = 0
  880. End With
  881. Selection.Copy
  882. Range("A2").Select
  883. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  884. :=False, Transpose:=False
  885. Application.CutCopyMode = False
  886. ActiveWorkbook.Worksheets("Action").ListObjects("ActionTable").Sort.SortFields. _
  887. Clear
  888. ActiveWorkbook.Worksheets("Action").ListObjects("ActionTable").Sort.SortFields. _
  889. Add2 Key:=Range("ActionTable[[#All],[Distance LY]]"), SortOn:=xlSortOnValues, _
  890. Order:=xlAscending, DataOption:=xlSortNormal
  891. With ActiveWorkbook.Worksheets("Action").ListObjects("ActionTable").Sort
  892. .Header = xlYes
  893. .MatchCase = False
  894. .Orientation = xlTopToBottom
  895. .SortMethod = xlPinYin
  896. .Apply
  897. End With
  898.  
  899. Range("ActionTable[[#Headers],[Systems]]").Offset(1).Select
  900.  
  901. End Sub
  902. Sub SortDistance()
  903.  
  904. On Error Resume Next
  905.  
  906. ActiveWorkbook.Worksheets("Action").ListObjects("ActionTable").Sort.SortFields. _
  907. Clear
  908. ActiveWorkbook.Worksheets("Action").ListObjects("ActionTable").Sort.SortFields. _
  909. Add2 Key:=Range("ActionTable[[#All],[Distance LY]]"), SortOn:=xlSortOnValues, _
  910. Order:=xlAscending, DataOption:=xlSortNormal
  911. With ActiveWorkbook.Worksheets("Action").ListObjects("ActionTable").Sort
  912. .Header = xlYes
  913. .MatchCase = False
  914. .Orientation = xlTopToBottom
  915. .SortMethod = xlPinYin
  916. .Apply
  917. End With
  918.  
  919. Range("ActionTable[[#Headers],[Systems]]").Offset(1).Select
  920.  
  921. End Sub
  922. Sub RemoveRecovery()
  923.  
  924. On Error Resume Next
  925.  
  926. If WorksheetFunction.CountA(Range("ActionTable[Systems]")) = 0 Then
  927. Exit Sub
  928. End If
  929.  
  930. Set rData = Sheets("Action").ListObjects("ActionTable")
  931.  
  932. rData.DataBodyRange.AutoFilter Field:=rData.ListColumns("In Recovery").Index, Criteria1:="Yes"
  933.  
  934. ' Empty filtered ActionTable fix
  935.  
  936. Rowz = rData.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1
  937.  
  938. If Rowz >= 1 Then
  939. rData.DataBodyRange.EntireRow.Delete
  940. End If
  941. rData.DataBodyRange.AutoFilter Field:=rData.ListColumns("In Recovery").Index
  942.  
  943. Range("ActionTable[[#Headers],[Systems]]").Offset(1).Select
  944.  
  945. End Sub
  946. Sub RemoveNPCfaction()
  947.  
  948. On Error Resume Next
  949.  
  950. If WorksheetFunction.CountA(Range("ActionTable[Systems]")) = 0 Then
  951. Exit Sub
  952. End If
  953.  
  954. Set rData = Sheets("Action").ListObjects("ActionTable")
  955.  
  956. rData.DataBodyRange.AutoFilter Field:=rData.ListColumns("Player Faction").Index, Criteria1:="No"
  957.  
  958. ' Empty filtered ActionTable fix
  959.  
  960. Rowz = rData.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1
  961.  
  962. If Rowz >= 1 Then
  963. rData.DataBodyRange.EntireRow.Delete
  964. End If
  965. rData.DataBodyRange.AutoFilter Field:=rData.ListColumns("Player Faction").Index
  966.  
  967. Range("ActionTable[[#Headers],[Systems]]").Offset(1).Select
  968.  
  969. End Sub
  970. Sub MarkAsPendingExp()
  971.  
  972. On Error Resume Next
  973.  
  974. Dim iCol
  975. iCol = Range("ActionTable[Faction]").Column
  976.  
  977. OriginalCell = ActiveCell.Address
  978.  
  979. Cells(ActiveCell.Row, iCol).Activate
  980.  
  981. PendingFaction = ActiveCell.Value
  982.  
  983. Set ActionData = Sheets("Action").ListObjects("ActionTable")
  984.  
  985. ActionData.DataBodyRange.AutoFilter Field:=ActionData.ListColumns("Faction").Index, Criteria1:=PendingFaction
  986.  
  987. Rowz = ActionData.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1
  988.  
  989. If Rowz >= 1 Then
  990. Range("ActionTable[Faction]").SpecialCells(xlCellTypeVisible).Select
  991.  
  992. With Selection.Font
  993. .Color = -16776961
  994. .TintAndShade = 0
  995. End With
  996. End If
  997.  
  998. ActionData.DataBodyRange.AutoFilter Field:=ActionData.ListColumns("Faction").Index
  999.  
  1000. Range(OriginalCell).Select
  1001.  
  1002. End Sub
  1003. Sub RemoveNoExpansion()
  1004.  
  1005. 'On Error Resume Next
  1006.  
  1007. OriginalCell = ActiveCell.Address
  1008.  
  1009. If WorksheetFunction.CountA(Range("ActionTable[Systems]")) = 0 Then
  1010. Exit Sub
  1011. End If
  1012.  
  1013. Set FactionData = Sheets("Action").ListObjects("ActionTable")
  1014.  
  1015. FactionData.DataBodyRange.AutoFilter Field:=FactionData.ListColumns("Faction").Index, Criteria1 _
  1016. :=RGB(255, 0, 0), Operator:=xlFilterFontColor
  1017.  
  1018. ' Empty filtered ActionTable fix
  1019.  
  1020. Rowz = FactionData.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1
  1021.  
  1022. If Rowz >= 1 Then
  1023. FactionData.DataBodyRange.EntireRow.Delete
  1024. End If
  1025. FactionData.DataBodyRange.AutoFilter Field:=FactionData.ListColumns("Faction").Index
  1026.  
  1027. Range(OriginalCell).Select
  1028.  
  1029. End Sub
  1030.  
  1031.  
  1032. Sub locateSystems()
  1033.  
  1034. Dim varResult As Variant
  1035. 'displays the save file dialog
  1036. varResult = Application.GetSaveAsFilename( _
  1037. InitialFileName:="", FileFilter:="JSON Files (*.json), *.json", Title:="Locate systems_populated.json on your PC")
  1038. 'checks to make sure the user hasn't canceled the dialog
  1039. If varResult <> False Then
  1040. Cells(47, 3) = varResult
  1041. End If
  1042.  
  1043. End Sub
  1044. Sub locateStations()
  1045.  
  1046. Dim varResult As Variant
  1047. 'displays the save file dialog
  1048. varResult = Application.GetSaveAsFilename( _
  1049. InitialFileName:="", FileFilter:="JSON Files (*.json), *.json", Title:="Locate stations.json on your PC")
  1050. 'checks to make sure the user hasn't canceled the dialog
  1051. If varResult <> False Then
  1052. Cells(46, 3) = varResult
  1053. End If
  1054.  
  1055. End Sub
  1056. Sub locateFactions()
  1057.  
  1058. Dim varResult As Variant
  1059. 'displays the save file dialog
  1060. varResult = Application.GetSaveAsFilename( _
  1061. InitialFileName:="", FileFilter:="JSON Files (*.json), *.json", Title:="Locate factions.json on your PC")
  1062. 'checks to make sure the user hasn't canceled the dialog
  1063. If varResult <> False Then
  1064. Cells(48, 3) = varResult
  1065. End If
  1066.  
  1067. End Sub
  1068.  
  1069.  
  1070. Sub CoreAnl()
  1071.  
  1072. ' Empty ActionTable check
  1073. If IsEmpty(Range("Action!B21").Value) = False Then
  1074. Sheets("Action").Select
  1075. Range("A21").Select
  1076. MsgBox "Please reset the Action Table before running an automated analysis"
  1077. Exit Sub
  1078. End If
  1079.  
  1080. ' CoreAnl1
  1081. Sheets("CoreAnl1").Select
  1082.  
  1083. 'Select Range
  1084. Range("B2").Select
  1085. If IsEmpty(Range("B3").Value) = True Then
  1086. Range("B2").Select
  1087. Else
  1088. Range(Selection, Selection.End(xlDown)).Select
  1089. End If
  1090.  
  1091. Selection.Copy
  1092.  
  1093. Sheets("Action").Select
  1094. Range("ActionTable[[#Headers],[Systems]]").Select
  1095.  
  1096. Set tbl = Sheets("Action").ListObjects("ActionTable")
  1097. lastRow = tbl.Range.Rows.Count
  1098.  
  1099. If IsEmpty(Range("B21").Value) = True Then
  1100. Selection.End(xlDown).Select
  1101.  
  1102. Else
  1103. Range("A" & lastRow).Offset(20).Select
  1104. End If
  1105.  
  1106. Selection.PasteSpecial
  1107. Range("A" & Rows.Count).End(xlUp).Offset(1).Select
  1108. Application.CutCopyMode = False
  1109.  
  1110.  
  1111. ' CoreAnl2
  1112. Sheets("CoreAnl2").Select
  1113. Range("B2").Select
  1114. Range(Selection, Selection.End(xlDown)).Select
  1115. Selection.Copy
  1116.  
  1117. Sheets("Action").Select
  1118. Range("ActionTable[[#Headers],[Systems]]").Select
  1119.  
  1120. Set tbl = Sheets("Action").ListObjects("ActionTable")
  1121. lastRow1 = tbl.Range.Rows.Count
  1122.  
  1123. If IsEmpty(Range("B21").Value) = True Then
  1124. Selection.End(xlDown).Select
  1125.  
  1126. Else
  1127. Range("A" & lastRow1).Offset(20).Select
  1128. End If
  1129.  
  1130. Selection.PasteSpecial
  1131. Range("A" & Rows.Count).End(xlUp).Offset(1).Select
  1132. Application.CutCopyMode = False
  1133.  
  1134. ' CoreAnl3
  1135. Sheets("CoreAnl3").Select
  1136. Range("B2").Select
  1137. Range(Selection, Selection.End(xlDown)).Select
  1138. Selection.Copy
  1139.  
  1140. Sheets("Action").Select
  1141. Range("ActionTable[[#Headers],[Systems]]").Select
  1142.  
  1143. Set tbl = Sheets("Action").ListObjects("ActionTable")
  1144. lastRow = tbl.Range.Rows.Count
  1145.  
  1146. If IsEmpty(Range("B21").Value) = True Then
  1147. Selection.End(xlDown).Select
  1148.  
  1149. Else
  1150. Range("A" & lastRow).Offset(20).Select
  1151. End If
  1152.  
  1153. Selection.PasteSpecial
  1154. Range("A" & Rows.Count).End(xlUp).Offset(1).Select
  1155. Application.CutCopyMode = False
  1156.  
  1157. ' CoreAnl4
  1158. Sheets("CoreAnl4").Select
  1159.  
  1160. 'Select Range
  1161. Range("B2").Select
  1162. If IsEmpty(Range("B3").Value) = True Then
  1163. Range("B2").Select
  1164. Else
  1165. Range(Selection, Selection.End(xlDown)).Select
  1166. End If
  1167.  
  1168. Selection.Copy
  1169.  
  1170. Sheets("Action").Select
  1171. Range("ActionTable[[#Headers],[Systems]]").Select
  1172.  
  1173. Set tbl = Sheets("Action").ListObjects("ActionTable")
  1174. lastRow = tbl.Range.Rows.Count
  1175.  
  1176. If IsEmpty(Range("B21").Value) = True Then
  1177. Selection.End(xlDown).Select
  1178.  
  1179. Else
  1180. Range("A" & lastRow).Offset(20).Select
  1181. End If
  1182.  
  1183. Selection.PasteSpecial
  1184. Range("A" & Rows.Count).End(xlUp).Offset(1).Select
  1185. Application.CutCopyMode = False
  1186.  
  1187. Range("A" & lastRow1).Offset(19).Select
  1188.  
  1189. Range(Selection, Selection.End(xlUp).Offset(1)).Select
  1190.  
  1191. With Selection.Font
  1192. .Color = -6279056
  1193. .TintAndShade = 0
  1194. End With
  1195. Range("A21").Select
  1196.  
  1197. End Sub
  1198. Sub FullAnl()
  1199.  
  1200. ' Empty ActionTable check
  1201. If IsEmpty(Range("Action!B21").Value) = False Then
  1202. Sheets("Action").Select
  1203. Range("A21").Select
  1204. MsgBox "Please reset the Action Table before running an automated analysis"
  1205. Exit Sub
  1206. End If
  1207.  
  1208. ' CoreAnl1
  1209. Sheets("CoreAnl1").Select
  1210.  
  1211. 'Select Range
  1212. Range("B2").Select
  1213. If IsEmpty(Range("B3").Value) = True Then
  1214. Range("B2").Select
  1215. Else
  1216. Range(Selection, Selection.End(xlDown)).Select
  1217. End If
  1218.  
  1219. Selection.Copy
  1220.  
  1221. Sheets("Action").Select
  1222. Range("ActionTable[[#Headers],[Systems]]").Select
  1223.  
  1224. Set tbl = Sheets("Action").ListObjects("ActionTable")
  1225. lastRow = tbl.Range.Rows.Count
  1226.  
  1227. If IsEmpty(Range("B21").Value) = True Then
  1228. Selection.End(xlDown).Select
  1229.  
  1230. Else
  1231. Range("A" & lastRow).Offset(20).Select
  1232. End If
  1233.  
  1234. Selection.PasteSpecial
  1235. Range("A" & Rows.Count).End(xlUp).Offset(1).Select
  1236. Application.CutCopyMode = False
  1237.  
  1238.  
  1239. ' CoreAnl2
  1240. Sheets("CoreAnl2").Select
  1241. Range("B2").Select
  1242. Range(Selection, Selection.End(xlDown)).Select
  1243. Selection.Copy
  1244.  
  1245. Sheets("Action").Select
  1246. Range("ActionTable[[#Headers],[Systems]]").Select
  1247.  
  1248. Set tbl = Sheets("Action").ListObjects("ActionTable")
  1249. lastRow1 = tbl.Range.Rows.Count
  1250.  
  1251. If IsEmpty(Range("B21").Value) = True Then
  1252. Selection.End(xlDown).Select
  1253.  
  1254. Else
  1255. Range("A" & lastRow1).Offset(20).Select
  1256. End If
  1257.  
  1258. Selection.PasteSpecial
  1259. Range("A" & Rows.Count).End(xlUp).Offset(1).Select
  1260. Application.CutCopyMode = False
  1261.  
  1262. ' CoreAnl3
  1263. Sheets("CoreAnl3").Select
  1264. Range("B2").Select
  1265. Range(Selection, Selection.End(xlDown)).Select
  1266. Selection.Copy
  1267.  
  1268. Sheets("Action").Select
  1269. Range("ActionTable[[#Headers],[Systems]]").Select
  1270.  
  1271. Set tbl = Sheets("Action").ListObjects("ActionTable")
  1272. lastRow = tbl.Range.Rows.Count
  1273.  
  1274. If IsEmpty(Range("B21").Value) = True Then
  1275. Selection.End(xlDown).Select
  1276.  
  1277. Else
  1278. Range("A" & lastRow).Offset(20).Select
  1279. End If
  1280.  
  1281. Selection.PasteSpecial
  1282. Range("A" & Rows.Count).End(xlUp).Offset(1).Select
  1283. Application.CutCopyMode = False
  1284.  
  1285. ' CoreAnl4
  1286. Sheets("CoreAnl4").Select
  1287.  
  1288. 'Select Range
  1289. Range("B2").Select
  1290. If IsEmpty(Range("B3").Value) = True Then
  1291. Range("B2").Select
  1292. Else
  1293. Range(Selection, Selection.End(xlDown)).Select
  1294. End If
  1295.  
  1296. Selection.Copy
  1297.  
  1298. Sheets("Action").Select
  1299. Range("ActionTable[[#Headers],[Systems]]").Select
  1300.  
  1301. Set tbl = Sheets("Action").ListObjects("ActionTable")
  1302. lastRow = tbl.Range.Rows.Count
  1303.  
  1304. If IsEmpty(Range("B21").Value) = True Then
  1305. Selection.End(xlDown).Select
  1306.  
  1307. Else
  1308. Range("A" & lastRow).Offset(20).Select
  1309. End If
  1310.  
  1311. Selection.PasteSpecial
  1312. Range("A" & Rows.Count).End(xlUp).Offset(1).Select
  1313. Application.CutCopyMode = False
  1314.  
  1315. Range("A" & lastRow1).Offset(19).Select
  1316.  
  1317. Range(Selection, Selection.End(xlUp).Offset(1)).Select
  1318.  
  1319. With Selection.Font
  1320. .Color = -6279056
  1321. .TintAndShade = 0
  1322. End With
  1323.  
  1324. ' ExtraAnl
  1325. Sheets("ExtraAnl").Select
  1326. Range("B2").Select
  1327. Range(Selection, Selection.End(xlDown)).Select
  1328. Selection.Copy
  1329.  
  1330. Sheets("Action").Select
  1331. Range("ActionTable[[#Headers],[Systems]]").Select
  1332.  
  1333. Set tbl = Sheets("Action").ListObjects("ActionTable")
  1334. lastRow = tbl.Range.Rows.Count
  1335.  
  1336. If IsEmpty(Range("B21").Value) = True Then
  1337. Selection.End(xlDown).Select
  1338.  
  1339. Else
  1340. Range("A" & lastRow).Offset(20).Select
  1341. End If
  1342.  
  1343. Selection.PasteSpecial
  1344. Range("A" & Rows.Count).End(xlUp).Offset(1).Select
  1345. Application.CutCopyMode = False
  1346.  
  1347. Range("A21").Select
  1348.  
  1349. End Sub
  1350. Sub CoreAnlPreTick()
  1351.  
  1352. ' Empty ActionTable check
  1353. If IsEmpty(Range("Action!B21").Value) = False Then
  1354. Sheets("Action").Select
  1355. Range("A21").Select
  1356. MsgBox "Please reset the Action Table before running an automated analysis"
  1357. Exit Sub
  1358. End If
  1359.  
  1360. ' CoreAnl1
  1361. Sheets("CoreAnl1").Select
  1362.  
  1363. 'Select Range
  1364. Range("B2").Select
  1365. If IsEmpty(Range("B3").Value) = True Then
  1366. Range("B2").Select
  1367. Else
  1368. Range(Selection, Selection.End(xlDown)).Select
  1369. End If
  1370.  
  1371. Selection.Copy
  1372.  
  1373. Sheets("Action").Select
  1374. Range("ActionTable[[#Headers],[Systems]]").Select
  1375.  
  1376. Set tbl = Sheets("Action").ListObjects("ActionTable")
  1377. lastRow = tbl.Range.Rows.Count
  1378.  
  1379. If IsEmpty(Range("B21").Value) = True Then
  1380. Selection.End(xlDown).Select
  1381.  
  1382. Else
  1383. Range("A" & lastRow).Offset(20).Select
  1384. End If
  1385.  
  1386. Selection.PasteSpecial
  1387. Range("A" & Rows.Count).End(xlUp).Offset(1).Select
  1388. Application.CutCopyMode = False
  1389.  
  1390.  
  1391. ' CoreAnl2
  1392. Sheets("CoreAnl2").Select
  1393. Range("B2").Select
  1394. Range(Selection, Selection.End(xlDown)).Select
  1395. Selection.Copy
  1396.  
  1397. Sheets("Action").Select
  1398. Range("ActionTable[[#Headers],[Systems]]").Select
  1399.  
  1400. Set tbl = Sheets("Action").ListObjects("ActionTable")
  1401. lastRow1 = tbl.Range.Rows.Count
  1402.  
  1403. If IsEmpty(Range("B21").Value) = True Then
  1404. Selection.End(xlDown).Select
  1405.  
  1406. Else
  1407. Range("A" & lastRow1).Offset(20).Select
  1408. End If
  1409.  
  1410. Selection.PasteSpecial
  1411. Range("A" & Rows.Count).End(xlUp).Offset(1).Select
  1412. Application.CutCopyMode = False
  1413.  
  1414. ' CoreAnl3
  1415. Sheets("CoreAnl3").Select
  1416. Range("B2").Select
  1417. Range(Selection, Selection.End(xlDown)).Select
  1418. Selection.Copy
  1419.  
  1420. Sheets("Action").Select
  1421. Range("ActionTable[[#Headers],[Systems]]").Select
  1422.  
  1423. Set tbl = Sheets("Action").ListObjects("ActionTable")
  1424. lastRow = tbl.Range.Rows.Count
  1425.  
  1426. If IsEmpty(Range("B21").Value) = True Then
  1427. Selection.End(xlDown).Select
  1428.  
  1429. Else
  1430. Range("A" & lastRow).Offset(20).Select
  1431. End If
  1432.  
  1433. Selection.PasteSpecial
  1434. Range("A" & Rows.Count).End(xlUp).Offset(1).Select
  1435. Application.CutCopyMode = False
  1436.  
  1437. ' CoreAnl4
  1438. Sheets("CoreAnl4").Select
  1439.  
  1440. 'Select Range
  1441. Range("B2").Select
  1442. If IsEmpty(Range("B3").Value) = True Then
  1443. Range("B2").Select
  1444. Else
  1445. Range(Selection, Selection.End(xlDown)).Select
  1446. End If
  1447.  
  1448. Selection.Copy
  1449.  
  1450. Sheets("Action").Select
  1451. Range("ActionTable[[#Headers],[Systems]]").Select
  1452.  
  1453. Set tbl = Sheets("Action").ListObjects("ActionTable")
  1454. lastRow = tbl.Range.Rows.Count
  1455.  
  1456. If IsEmpty(Range("B21").Value) = True Then
  1457. Selection.End(xlDown).Select
  1458.  
  1459. Else
  1460. Range("A" & lastRow).Offset(20).Select
  1461. End If
  1462.  
  1463. Selection.PasteSpecial
  1464. Range("A" & Rows.Count).End(xlUp).Offset(1).Select
  1465. Application.CutCopyMode = False
  1466.  
  1467. Range("A" & lastRow1).Offset(19).Select
  1468.  
  1469. Range(Selection, Selection.End(xlUp).Offset(1)).Select
  1470.  
  1471. With Selection.Font
  1472. .Color = -6279056
  1473. .TintAndShade = 0
  1474. End With
  1475.  
  1476. With ActiveSheet.ListObjects("ActionTable")
  1477. .Range.AutoFilter Field:=9, Criteria1:=">" & Range("Settings!B32").Value
  1478.  
  1479.  
  1480. ' Empty filtered ActionTable fix
  1481. On Error Resume Next
  1482.  
  1483. Set ws = Sheets("Action").ListObjects("ActionTable")
  1484. Rowz = ws.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1
  1485.  
  1486. If Rowz >= 1 Then
  1487. .DataBodyRange.EntireRow.Delete
  1488. End If
  1489. .Range.AutoFilter Field:=9
  1490. End With
  1491.  
  1492. Range("A21").Select
  1493.  
  1494. End Sub
  1495. Sub FullAnlPreTick()
  1496.  
  1497. ' Empty ActionTable check
  1498. If IsEmpty(Range("Action!B21").Value) = False Then
  1499. Sheets("Action").Select
  1500. Range("A21").Select
  1501. MsgBox "Please reset the Action Table before running an automated analysis"
  1502. Exit Sub
  1503. End If
  1504.  
  1505. ' CoreAnl1
  1506. Sheets("CoreAnl1").Select
  1507.  
  1508. 'Select Range
  1509. Range("B2").Select
  1510. If IsEmpty(Range("B3").Value) = True Then
  1511. Range("B2").Select
  1512. Else
  1513. Range(Selection, Selection.End(xlDown)).Select
  1514. End If
  1515.  
  1516. Selection.Copy
  1517.  
  1518. Sheets("Action").Select
  1519. Range("ActionTable[[#Headers],[Systems]]").Select
  1520.  
  1521. Set tbl = Sheets("Action").ListObjects("ActionTable")
  1522. lastRow = tbl.Range.Rows.Count
  1523.  
  1524. If IsEmpty(Range("B21").Value) = True Then
  1525. Selection.End(xlDown).Select
  1526.  
  1527. Else
  1528. Range("A" & lastRow).Offset(20).Select
  1529. End If
  1530.  
  1531. Selection.PasteSpecial
  1532. Range("A" & Rows.Count).End(xlUp).Offset(1).Select
  1533. Application.CutCopyMode = False
  1534.  
  1535.  
  1536. ' CoreAnl2
  1537. Sheets("CoreAnl2").Select
  1538. Range("B2").Select
  1539. Range(Selection, Selection.End(xlDown)).Select
  1540. Selection.Copy
  1541.  
  1542. Sheets("Action").Select
  1543. Range("ActionTable[[#Headers],[Systems]]").Select
  1544.  
  1545. Set tbl = Sheets("Action").ListObjects("ActionTable")
  1546. lastRow1 = tbl.Range.Rows.Count
  1547.  
  1548. If IsEmpty(Range("B21").Value) = True Then
  1549. Selection.End(xlDown).Select
  1550.  
  1551. Else
  1552. Range("A" & lastRow1).Offset(20).Select
  1553. End If
  1554.  
  1555. Selection.PasteSpecial
  1556. Range("A" & Rows.Count).End(xlUp).Offset(1).Select
  1557. Application.CutCopyMode = False
  1558.  
  1559. ' CoreAnl3
  1560. Sheets("CoreAnl3").Select
  1561. Range("B2").Select
  1562. Range(Selection, Selection.End(xlDown)).Select
  1563. Selection.Copy
  1564.  
  1565. Sheets("Action").Select
  1566. Range("ActionTable[[#Headers],[Systems]]").Select
  1567.  
  1568. Set tbl = Sheets("Action").ListObjects("ActionTable")
  1569. lastRow = tbl.Range.Rows.Count
  1570.  
  1571. If IsEmpty(Range("B21").Value) = True Then
  1572. Selection.End(xlDown).Select
  1573.  
  1574. Else
  1575. Range("A" & lastRow).Offset(20).Select
  1576. End If
  1577.  
  1578. Selection.PasteSpecial
  1579. Range("A" & Rows.Count).End(xlUp).Offset(1).Select
  1580. Application.CutCopyMode = False
  1581.  
  1582. ' CoreAnl4
  1583. Sheets("CoreAnl4").Select
  1584.  
  1585. 'Select Range
  1586. Range("B2").Select
  1587. If IsEmpty(Range("B3").Value) = True Then
  1588. Range("B2").Select
  1589. Else
  1590. Range(Selection, Selection.End(xlDown)).Select
  1591. End If
  1592.  
  1593. Selection.Copy
  1594.  
  1595. Sheets("Action").Select
  1596. Range("ActionTable[[#Headers],[Systems]]").Select
  1597.  
  1598. Set tbl = Sheets("Action").ListObjects("ActionTable")
  1599. lastRow = tbl.Range.Rows.Count
  1600.  
  1601. If IsEmpty(Range("B21").Value) = True Then
  1602. Selection.End(xlDown).Select
  1603.  
  1604. Else
  1605. Range("A" & lastRow).Offset(20).Select
  1606. End If
  1607.  
  1608. Selection.PasteSpecial
  1609. Range("A" & Rows.Count).End(xlUp).Offset(1).Select
  1610. Application.CutCopyMode = False
  1611.  
  1612. Range("A" & lastRow1).Offset(19).Select
  1613.  
  1614. Range(Selection, Selection.End(xlUp).Offset(1)).Select
  1615.  
  1616. With Selection.Font
  1617. .Color = -6279056
  1618. .TintAndShade = 0
  1619. End With
  1620.  
  1621. ' ExtraAnl
  1622. Sheets("ExtraAnl").Select
  1623. Range("B2").Select
  1624. Range(Selection, Selection.End(xlDown)).Select
  1625. Selection.Copy
  1626.  
  1627. Sheets("Action").Select
  1628. Range("ActionTable[[#Headers],[Systems]]").Select
  1629.  
  1630. Set tbl = Sheets("Action").ListObjects("ActionTable")
  1631. lastRow = tbl.Range.Rows.Count
  1632.  
  1633. If IsEmpty(Range("B21").Value) = True Then
  1634. Selection.End(xlDown).Select
  1635.  
  1636. Else
  1637. Range("A" & lastRow).Offset(20).Select
  1638. End If
  1639.  
  1640. Selection.PasteSpecial
  1641. Range("A" & Rows.Count).End(xlUp).Offset(1).Select
  1642. Application.CutCopyMode = False
  1643.  
  1644. With ActiveSheet.ListObjects("ActionTable")
  1645. .Range.AutoFilter Field:=9, Criteria1:=">" & Range("Settings!B32").Value
  1646.  
  1647. ' Empty filtered ActionTable fix
  1648. On Error Resume Next
  1649.  
  1650. Set ws = Sheets("Action").ListObjects("ActionTable")
  1651. Rowz = ws.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1
  1652.  
  1653. If Rowz >= 1 Then
  1654. .DataBodyRange.EntireRow.Delete
  1655. End If
  1656. .Range.AutoFilter Field:=9
  1657. End With
  1658.  
  1659. Range("A21").Select
  1660.  
  1661. End Sub
  1662.  
  1663.  
  1664. Sub PastePubHValues()
  1665.  
  1666. On Error GoTo ErrorHandler
  1667.  
  1668. Range("PubHTable[[#Headers],[Systems]]").Select
  1669.  
  1670. Set tbl = Sheets("LivePubH").ListObjects("PubHTable")
  1671. lastRow = tbl.Range.Rows.Count
  1672.  
  1673. If IsEmpty(Range("B21").Value) = True Then
  1674. Range("PubHTable[Systems]").Select
  1675.  
  1676. Else
  1677. Range("A" & lastRow).Offset(20).Select
  1678. End If
  1679.  
  1680. ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
  1681. False, NoHTMLFormatting:=True
  1682. Range("A" & Rows.Count).End(xlUp).Offset(1).Select
  1683. Application.CutCopyMode = False
  1684.  
  1685. ' Tidy up EDDB leading characters
  1686.  
  1687. Columns("A:A").Select
  1688. Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
  1689. SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
  1690. ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
  1691.  
  1692. Range("A21").Select
  1693.  
  1694. ErrorHandler:
  1695. Application.CutCopyMode = False
  1696. Exit Sub
  1697.  
  1698. End Sub
  1699. Sub ResetPubHTable()
  1700.  
  1701. On Error Resume Next
  1702. Range("A21:A1048576").Select
  1703.  
  1704. Application.CutCopyMode = False
  1705. Selection.ClearContents
  1706. ActiveSheet.Range("A21").Select
  1707.  
  1708. With ActiveSheet.ListObjects("PubHTable")
  1709. .Range.AutoFilter Field:=1, Criteria1:="="
  1710. .DataBodyRange.EntireRow.Delete
  1711. .Range.AutoFilter Field:=1
  1712. End With
  1713.  
  1714. Range("A20").Select
  1715. Range("A21").Select
  1716.  
  1717. End Sub
  1718. Sub PastePirAValues()
  1719.  
  1720. On Error GoTo ErrorHandler
  1721.  
  1722. Range("PirATable[[#Headers],[Systems]]").Select
  1723.  
  1724. Set tbl = Sheets("LivePirA").ListObjects("PirATable")
  1725. lastRow = tbl.Range.Rows.Count
  1726.  
  1727. If IsEmpty(Range("B21").Value) = True Then
  1728. Range("PirATable[Systems]").Select
  1729.  
  1730. Else
  1731. Range("A" & lastRow).Offset(20).Select
  1732. End If
  1733.  
  1734. ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
  1735. False, NoHTMLFormatting:=True
  1736. Range("A" & Rows.Count).End(xlUp).Offset(1).Select
  1737. Application.CutCopyMode = False
  1738.  
  1739. ' Tidy up EDDB leading characters
  1740.  
  1741. Columns("A:A").Select
  1742. Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
  1743. SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
  1744. ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
  1745.  
  1746. Range("A21").Select
  1747.  
  1748. ErrorHandler:
  1749. Application.CutCopyMode = False
  1750. Exit Sub
  1751.  
  1752. End Sub
  1753. Sub ResetPirATable()
  1754.  
  1755. On Error Resume Next
  1756. Range("A21:A1048576").Select
  1757.  
  1758. Application.CutCopyMode = False
  1759. Selection.ClearContents
  1760. ActiveSheet.Range("A21").Select
  1761.  
  1762. With ActiveSheet.ListObjects("PirATable")
  1763. .Range.AutoFilter Field:=1, Criteria1:="="
  1764. .DataBodyRange.EntireRow.Delete
  1765. .Range.AutoFilter Field:=1
  1766. End With
  1767.  
  1768. Range("A20").Select
  1769. Range("A21").Select
  1770.  
  1771. End Sub
  1772.  
  1773.  
  1774. Sub CopyMinorFaction()
  1775. On Error Resume Next
  1776.  
  1777. FactionCell = ActiveCell.Address
  1778.  
  1779. Selection.Copy
  1780. Range("D8").Select
  1781. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  1782. :=False, Transpose:=False
  1783. Application.CutCopyMode = False
  1784. Range(FactionCell).Offset(1).Select
  1785. End Sub
  1786. Sub FactionToAction()
  1787. On Error Resume Next
  1788.  
  1789. FactionCell = ActiveCell.Address
  1790.  
  1791. With ActiveSheet.ListObjects("FactionResearch")
  1792. .Range.AutoFilter Field:=3, Criteria1:=">=" & Range("J8").Value
  1793.  
  1794. ' Empty filtered ActionTable fix
  1795. On Error Resume Next
  1796.  
  1797. Set ws = ActiveSheet.ListObjects("FactionResearch")
  1798. Rowz = ws.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1
  1799.  
  1800. If Rowz >= 1 Then
  1801. Range("FactionResearch[[Systems]]").SpecialCells(xlCellTypeVisible).Select
  1802.  
  1803. Selection.Copy
  1804. Sheets("Action").Select
  1805. Range("ActionTable[[#Headers],[Systems]]").Select
  1806.  
  1807. Set tbl = Sheets("Action").ListObjects("ActionTable")
  1808. lastRow = tbl.Range.Rows.Count
  1809.  
  1810. If IsEmpty(Range("B21").Value) = True Then
  1811. Selection.End(xlDown).Select
  1812.  
  1813. Else
  1814. Range("A" & lastRow).Offset(20).Select
  1815. End If
  1816.  
  1817. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  1818. Range("A" & Rows.Count).End(xlUp).Offset(1).Select
  1819. Application.CutCopyMode = False
  1820.  
  1821. End If
  1822.  
  1823. Sheets("Factions").Select
  1824. Range("FactionResearch[[#Headers],[Systems]]").Select
  1825.  
  1826. .Range.AutoFilter Field:=3
  1827.  
  1828. 'MsgBox Rowz
  1829.  
  1830. End With
  1831.  
  1832. Range(FactionCell).Select
  1833.  
  1834. End Sub
  1835.  
  1836.  
  1837. #If Win64 Then 'If the system is in 64b
  1838. Private Declare PtrSafe Function MsgBoxTimeout _
  1839. Lib "user32" _
  1840. Alias "MessageBoxTimeoutA" ( _
  1841. ByVal hwnd As LongPtr, _
  1842. ByVal lpText As String, _
  1843. ByVal lpCaption As String, _
  1844. ByVal wType As VbMsgBoxStyle, _
  1845. ByVal wlange As Long, _
  1846. ByVal dwTimeout As Long) _
  1847. As Long
  1848. #Else 'if it's in 32b
  1849. Private Declare Function MsgBoxTimeout _
  1850. Lib "user32" _
  1851. Alias "MessageBoxTimeoutA" ( _
  1852. ByVal hwnd As Long, _
  1853. ByVal lpText As String, _
  1854. ByVal lpCaption As String, _
  1855. ByVal wType As VbMsgBoxStyle, _
  1856. ByVal wlange As Long, _
  1857. ByVal dwTimeout As Long) _
  1858. As Long
  1859. #End If
  1860.  
  1861. Sub Workbook_RefreshAll()
  1862.  
  1863. Application.Calculation = xlManual
  1864.  
  1865. Dim SrchRng1 As Range
  1866. Dim SrchRng2 As Range
  1867. Dim SrchRng3 As Range
  1868.  
  1869. Set SrchRng1 = Range("D46")
  1870. Set SrchRng2 = Range("D47")
  1871. Set SrchRng3 = Range("D48")
  1872.  
  1873. If InStr(SrchRng1.Value, "Missing") > 0 Then
  1874. MsgBox "Missing file. Please locate stations.json"
  1875. Exit Sub
  1876. End If
  1877.  
  1878. If InStr(SrchRng2.Value, "Missing") > 0 Then
  1879. MsgBox "Missing file. Please locate systems_populated.json"
  1880. Exit Sub
  1881. End If
  1882.  
  1883. If InStr(SrchRng3.Value, "Missing") > 0 Then
  1884. MsgBox "Missing file. Please locate factions.json"
  1885. Exit Sub
  1886. End If
  1887.  
  1888. Application.CommandBars("Queries and Connections").Visible = True
  1889. Application.CommandBars("Queries and Connections").Width = 300
  1890.  
  1891. With ActiveWorkbook.Connections("Query - Systems").OLEDBConnection
  1892. .BackgroundQuery = False
  1893. .Refresh
  1894. End With
  1895.  
  1896. With ActiveWorkbook.Connections("Query - Boom").OLEDBConnection
  1897. .BackgroundQuery = False
  1898. .Refresh
  1899. End With
  1900.  
  1901. With ActiveWorkbook.Connections("Query - Investment").OLEDBConnection
  1902. .BackgroundQuery = False
  1903. .Refresh
  1904. End With
  1905.  
  1906. With ActiveWorkbook.Connections("Query - Expansion").OLEDBConnection
  1907. .BackgroundQuery = False
  1908. .Refresh
  1909. End With
  1910.  
  1911. With ActiveWorkbook.Connections("Query - CivLib").OLEDBConnection
  1912. .BackgroundQuery = False
  1913. .Refresh
  1914. End With
  1915.  
  1916. With ActiveWorkbook.Connections("Query - PublicH").OLEDBConnection
  1917. .BackgroundQuery = False
  1918. .Refresh
  1919. End With
  1920.  
  1921. With ActiveWorkbook.Connections("Query - PirateA").OLEDBConnection
  1922. .BackgroundQuery = False
  1923. .Refresh
  1924. End With
  1925.  
  1926. With ActiveWorkbook.Connections("Query - PendBoom").OLEDBConnection
  1927. .BackgroundQuery = False
  1928. .Refresh
  1929. End With
  1930.  
  1931. With ActiveWorkbook.Connections("Query - PExpansion").OLEDBConnection
  1932. .BackgroundQuery = False
  1933. .Refresh
  1934. End With
  1935.  
  1936. With ActiveWorkbook.Connections("Query - PendCivL").OLEDBConnection
  1937. .BackgroundQuery = False
  1938. .Refresh
  1939. End With
  1940.  
  1941. With ActiveWorkbook.Connections("Query - Recovering").OLEDBConnection
  1942. .BackgroundQuery = False
  1943. .Refresh
  1944. End With
  1945.  
  1946. With ActiveWorkbook.Connections("Query - SystemFilter").OLEDBConnection
  1947. .BackgroundQuery = False
  1948. .Refresh
  1949. End With
  1950.  
  1951. Call MsgBoxTimeout(0, "Excel will pause for a few minutes on the Anaysis data load. Performing 226,672 calculations.", "Auto Close MsgBox", vbInformation, 0, 4000)
  1952.  
  1953. With ActiveWorkbook.Connections("Query - Analysis").OLEDBConnection
  1954. .BackgroundQuery = False
  1955. .Refresh
  1956. End With
  1957.  
  1958. With ActiveWorkbook.Connections("Query - CoreAnl1").OLEDBConnection
  1959. .BackgroundQuery = False
  1960. .Refresh
  1961. End With
  1962.  
  1963. With ActiveWorkbook.Connections("Query - CoreAnl2").OLEDBConnection
  1964. .BackgroundQuery = False
  1965. .Refresh
  1966. End With
  1967.  
  1968. With ActiveWorkbook.Connections("Query - CoreAnl3").OLEDBConnection
  1969. .BackgroundQuery = False
  1970. .Refresh
  1971. End With
  1972.  
  1973. With ActiveWorkbook.Connections("Query - CoreAnl4").OLEDBConnection
  1974. .BackgroundQuery = False
  1975. .Refresh
  1976. End With
  1977.  
  1978. With ActiveWorkbook.Connections("Query - ExtraAnl").OLEDBConnection
  1979. .BackgroundQuery = False
  1980. .Refresh
  1981. End With
  1982.  
  1983. With ActiveWorkbook.Connections("Query - PlayerFactions").OLEDBConnection
  1984. .BackgroundQuery = False
  1985. .Refresh
  1986. End With
  1987.  
  1988. With ActiveWorkbook.Connections("Query - ExpansionFactions").OLEDBConnection
  1989. .BackgroundQuery = False
  1990. .Refresh
  1991. End With
  1992.  
  1993. Application.Wait (Now + TimeValue("0:00:05"))
  1994.  
  1995. Application.Calculation = xlAutomatic
  1996.  
  1997. Beep
  1998. MsgBox "EDDB Data Load Complete"
  1999.  
  2000. End Sub
  2001.  
  2002. +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!