This file allows one click creation of autobuy lists using user-specified parameters and using up-to-date information from the neocodex database.
Intro:
I've been using Abrosia's autobuyer for ages now, and being a lurker and thus a normal member I had to use my own AB lists when I wanted to shop at a specific shop. The main issue with AB lists is that it is it quickly goes out of date, with the constant fluctuations in item prices. Months ago, I threw together this excel program to let me quickly create AB lists for any store I wanted. I felt like I needed to give something back to the community for creating such wonderful programs, especially Abrosia. I polished up my excel file to make it user friendly and pretty and did some basic quality assurance testing. I hope you enjoy it!
Requirements:
Excel 2007 or 2010 - Not compatible with older excels. If there is enough demand, I will consider making a version compatible with the older excels.
Windows - I didn't and don't plan to do any testing on Mac or Linux, sorry.
TheV's ABList Gen.xlsm
Download: http://www.megaupload.com/?d=G4HY3LJ6
Notes:
Just so you know your risks, let me warn you that macros in excel files can be very powerful and can be used for very malicious purposes. I assure you that my VBA scripts are completely safe and will perform only the functions advertised. I invite all users to review my code. It is comprised of just three relatively short functions which are all fully documented. You can view the VBA code by first enabling the developer ribbon (file -> options -> customize ribbon -> developer -> OK) and then clicking the Visual Basic button on the developer ribbon. In my file, macros are used to fetch data from the neocodex item database, write txt files to the root directory and overwrite txt files in the root directory (overwriting will always be preceded by a warning).
The sheets in this file are all protected, to prevent newbies from accidentally editing important cells. There is no password on the protection, so anyone can unprotect and edit the file freely (I don't mind).
Features List:
- One click generation of AB list using up-to-date values from the Neocodex database
- Filter based on item name, min/max item value, min/max item rarity, shop, minimum profit and percent return
- View which items will be added onto your list and which won't be added
- Make lists for shops that are not selectable in the neocodex item database (110, 111, 112, 113, 114), not that they're very profitable
- Make searches in the neocodex database from the excel file
Setting it up:
1. Download the file xlsm file and place it in any directory you wish. All lists generated will be put in the same directory.
2. Log in to Neocodex on internet explorer. Excel needs full access to the neocodex item database, and web queries in excel only use internet explorer.
3. Open the file and enable macros. See the note above regarding macros. If you wish to review the code, you can do so now, before enabling macros.
Spoiler
4. That's all! You can now start generating AB lists.
How to use it:
The program is straight forward, so you can go ahead and use it and refer here if you're not sure about something.
You can switch sheets using the tabs at the bottom. The two main sheets of interest for the user is the Input sheet and the Data sheet.
Input Sheet
Input all your parameters and click Generate List. It's that easy.
Spoiler
Input Boxes
Choose Shop - Use the selection list to choose the shop you want to buy from. In the picture I am buying from the food shop.
Item name contains - If you want to buy a specific type of item only, then type it in here, otherwise leave it blank. I am looking to only buy pizzas.
Minimum percent return - This is how much you make selling the item relative to how much you spent buying it. For example, if it is 200%, it means you will only buy items for which you can sell at least twice as much as you spent. Less than 100% means negative profit. I have 300%, so I only want items that can triple my investment.
Minimum Profit - The minimum profit for which you wish to buy the items. I am only going to buy items that give me at least 2500.
Max/Min Value - The maximum and minimum market values of the items you buy. This is the value you will likely sell the item for. If there is a maximum, there must be a minimum, otherwise both should be blank. I put in arbitrary values just for example.
Max/Min Rarity - The max and min rarities. Again, if you have a maximum, there must be a minimum, otherwise leave both blank. I put in arbitrary values for example.
Buttons
Generate List - This will output the AB list to the directory the xlsm file is in. A confirmation box will appear with the AB list path.
Spoiler
Refresh Data - This will refresh the data sheet using the parameters you provided and bring you to the data sheet.
Data Sheet
The data sheet shows what you would get if you were to search the neocodex database. It also calculates a couple other values.
Spoiler
Item Name - Name of the item to be added to the AB list. You can see that all items have "pizza" in the name.
Actual Value - Value you get when you sell the item. You can see that only items between the values 725 and 100000 specified are shown.
Profit - Profit of the item. Unprofitable items have red text.
Approximate Purchase Price - This is the difference between the profit and the actual value of the item. It is an estimate of how much you will be paying for the item from the shop. It is not exactly accurate, but it gives you a general idea.
Percent Return - The percent return is calculated using the approximate purchase price and the actual value.
On List? - Indicates whether the item will be put on the AB list (if it meets all of the input parameters). Here, only the first two items are going to be put in the AB list since the others don't meet the profit and percent return requirements.
Page - The data sheet only shows 100 items at a time (like the actual neocodex database). To view a different page, change the page number and click Refresh.
Importing into Ambrosia
Spoiler
1. Open Ambrosia and go to program settings
2. Select the Main Shop Settings tab
3. Select "Load a Manual Shop List"
4. Click Import List and import the generated list
5. Click OK and start ABing!
Afterword
Due to how much prices can fluctuate, I recommended updating your list at least once a week. It's just a click of a button, so it should be quick and easy. I do it every time I start Ambrosia.
Admittedly I didn't do thorough quality assurance tests, but the code is simple enough that I don't think there will be any major errors. Just post here and I'll try and solve them. I wouldn't call myself an excel or VBA expert, so I welcome any suggestions on things I could do better.
