Page 1 of 2

Mercari sales - Generating tax form

Posted: Tue Feb 09, 2021 6:25 am
by Viralriver
Hey all, I feel this is pretty specific but in case it helps someone I'll link it here. So I sell quite a lot on Mercari, and my accountant has asked me to generate an excel spreadsheet with all of my sales data. Anyone who has used Mercari knows their UI is terrible, there's no way to download this data, and you essentially have to load each transaction separately which can take hours even if you have as few sales as me (<100).

To help with this, I created a quick simple script that will generate a table with all your sales (item name, ID, sales price, etc) in an Excel-pastable format. If you sell on Mercari, you may want to try it out if you are filing taxes.

Notes: I quickly whipped this up, it's not pretty but it does the job (for me at least). I'll clean it up and make it more useful (date searching, and some other features) when I have time. Also, will post to my GitHub to track it, but for now will just dump the code. This will probably work in Firefox as well, but only tested in Chrome.

Steps
  1. Go to your sold items page (出品した商品)
  2. In the browser, open up the developer tools
    Screen Shot 2021-02-09 at 15.19.02.png
  3. Click the console tab if it's not already open. If there are errors and warnings here, you can ignore them :)
    Screen Shot 2021-02-09 at 15.19.37.png
  4. Paste the code below and hit "Enter"
  5. Open up Excel and "paste"
    Screen Shot 2021-02-09 at 15.24.01.png
Code

Code: Select all

class Mercari {
    constructor() {
        this.items = {};
        this.finished = false;
        this.page = $("body");
        this.page_number = 1;
        this.load_page();
    }

    static order = [
        "商品",
        "商品ID",
        "商品金額",
        "販売手数料",
        "配送料",
        "販売利益",
        "購入日時"
    ];

    load_page() {
        if(!this.are_there_more_items()) {
            console.log("Extraction complete - no more items on this page exist.");
            this.all_items_loaded();
        } else {
            console.log("Extracting items on page " + this.page_number);
            this.load_items_on_page();
        }
        return null;
    }

    are_there_more_items() {
        return !this.page.find("li.mypage-item-not-found.bold").length;
    }

    load_items_on_page() {
        var item_links = this.page.find("#mypage-tab-transaction-old li.js-mypage-item a.mypage-item-link.has-button");
        var no_of_items = item_links.length;
        var item_link, url, timestamp;
        var that = this;
        var items_on_this_page = {};
        
        for(var i = 0; i < no_of_items; i++) {
            item_link = item_links.eq(i);
            url = item_link.attr("href");
            timestamp = item_link.parent().data("timestamp");
            
            $.get(url, (function(timestamp) {
                return function(data) {
                    items_on_this_page[timestamp] = that.get_item_data($(data));
                    var loaded_items = Object.keys(items_on_this_page).length;
                    if(loaded_items == no_of_items) {
                        console.log("-> Loaded all items on this page");
                        that.items = $.extend(that.items, items_on_this_page);
                        that.get_next_page();
                    } else {
                        console.log("-> Loaded item #" + loaded_items + "/" + no_of_items);
                    }
                };
            })(timestamp));
        }
    }

    get_next_page() {
        this.page_number += 1;
        var next_page_button = this.page.find("ul.mypage-history.pager li.pager-next.pager-cell a");
        if(next_page_button.length) {
            var that = this;
            var href = next_page_button.first().attr("href");
            console.log("Loading page " + this.page_number);
            $.get(href, function(data) {
                console.log("-> Page loaded");
                that.page = $(data);
                that.load_page();
            });
        } else {
            console.log("Extraction process complete - no more pages");
            this.all_items_loaded();
        }
    }

    get_item_data(data) {
        var item = {};
        data.find("ul.transact-info-table > li").each(function(){
            var that = $(this);
            var header = that.find("span").first().text().trim();
            var content = that.find("ul li");
            var text;
        
            switch(header) {
                case "商品":
                    text = content.find("div.transact-info-item.bold").first().html().split("<br>")[0].trim();
                    break;
                case "送料":
                    return;
                case "販売手数料":
                case "配送料":
                case "販売利益":
                    text = +(Mercari.to_raw_num(content.first().text().trim()));
                    break;
                case "購入日時":
                case "商品ID":
                    text = content.first().text().trim();
                    break;
                default:
                    console.log("Error " + header);
                    return;
            }
        
            item[header] = text;
        
        });
        
        item["商品金額"] = item["販売利益"] + item["配送料"] + item["販売手数料"];

        var ordered_item = [];
        for(var order_id in Mercari.order) {
            ordered_item.push(item[Mercari.order[order_id]]);
        }

        return ordered_item;
    }

    static to_raw_num(price) {
        price = price.replace("¥", "");
        price = price.replace(/,/g, "");
        return price;
    }

    all_items_loaded() {
        if(this.finished) {
            throw Error("Already finished!");
        }
        // Ensure this code only gets run once.
        this.finished = true; 

        console.log(Object.keys(this.items).length + " items extracted.");

        // Put each item into tab format
        this.tab_items();

        // reorder items
        this.sort_items();

        // copy to clipboard
        Mercari.copy_to_clipboard(this.items.join("\r\n"));
        console.log("Items copied to clipboard.");
    }

    tab_items() {
        var tabbed_items = {};
        for(var item_id in this.items) {
            tabbed_items[item_id] = this.items[item_id].join("\t")
        }
        this.items = tabbed_items;
    }

    sort_items() {
        var keys = Object.keys(this.items).sort();
        var sorted_items = [];
        for(const key of keys){
            sorted_items.push(this.items[key]);
        }
        this.items = sorted_items;
    }

    static copy_to_clipboard(content) {
        var $temp = $("<textarea>");
        $("body").append($temp);
        $temp.val(content).select();
        document.execCommand("copy");
        $temp.remove();
    }
}

var mercari = new Mercari();
Hope someone can find this useful :) If not, I needed it anyway haha

Re: Mercari sales - Generating tax form

Posted: Fri Feb 12, 2021 4:10 am
by MajesticSoup
How long did it take you to get to where you are as a dev? I would not have been able to come up with that on my own, but a year ago I was trying to make a script that automatically lowered the price of all my listings that would meet a certain condition, and I failed hard.

Re: Mercari sales - Generating tax form

Posted: Fri Feb 12, 2021 7:26 am
by Viralriver
Not really a developer any more, but I worked on website/forum modifications a lot when I was much younger. From the age of 12-20 ish I guess, it was like my hobby but basically spent every day doing it. Making something like this is just fun now as I don't get to do stuff like this at all any more. Saying that, I tried using some new things in JS that I'd never touched before as it's updated a lot since I last used it, so I wouldn't say it's the prettiest code either haha.

If you can be more specific with what you were wanting, I could try and whip something up when I get some free time :) . It would generally be a 'pastable' solution like what I have here. Having something run 'in the background' is quite a bit more difficult to do. There is stuff like tampermonkey/greasemonkey which can help with this, but I've always found them a pain to work with (when mixing in things like jQuery).

Re: Mercari sales - Generating tax form

Posted: Fri Feb 12, 2021 12:59 pm
by glimmer
That's really cool! I don't use Mercari much due to language barrier, but I just encountered a similar problem with Amazon Japan and posted about it below:

viewtopic.php?f=8&t=1367&sid=3c2a0f0f11 ... 393be1a5de

Am curious if you have any thoughts on it? Feel free to reply on that thread so I'm not highjacking yours.

Re: Mercari sales - Generating tax form

Posted: Sat Feb 13, 2021 9:29 am
by Viralriver
Just had a quick try to do something, but there's quite a few restrictions on AJAX (the 'technology' that allows me to load other pages from a single page) on the Amazon site. It's probably possible, but will take some more tinkering around. I'll give it another go when I have a little more time. I work at Amazon too so might ask internally to see if there's any plans for this in the future anyway.

Re: Mercari sales - Generating tax form

Posted: Sat Feb 13, 2021 9:53 am
by glimmer
What are the chances?? How cool! I'm guessing this is a problem that affects countless businesses in Japan, but perhaps they just submit their business credit card statements instead. Thank you so much!

Re: Mercari sales - Generating tax form

Posted: Sun Feb 14, 2021 11:23 pm
by MajesticSoup
Viralriver wrote: Fri Feb 12, 2021 7:26 am If you can be more specific with what you were wanting, I could try and whip something up when I get some free time :) . It would generally be a 'pastable' solution like what I have here. Having something run 'in the background' is quite a bit more difficult to do. There is stuff like tampermonkey/greasemonkey which can help with this, but I've always found them a pain to work with (when mixing in things like jQuery).
I was wanting to make a basic script that has this kind of logic:

- loops through all my existing listings
- if listing age > 30 days
- if listing price > 1000 yen
lower price of listing by 100 yen (this should effectively bump the listing)

Re: Mercari sales - Generating tax form

Posted: Mon Feb 15, 2021 1:18 am
by Viralriver
MajesticSoup wrote: Sun Feb 14, 2021 11:23 pm
Viralriver wrote: Fri Feb 12, 2021 7:26 am If you can be more specific with what you were wanting, I could try and whip something up when I get some free time :) . It would generally be a 'pastable' solution like what I have here. Having something run 'in the background' is quite a bit more difficult to do. There is stuff like tampermonkey/greasemonkey which can help with this, but I've always found them a pain to work with (when mixing in things like jQuery).
I was wanting to make a basic script that has this kind of logic:

- loops through all my existing listings
- if listing age > 30 days
- if listing price > 1000 yen
lower price of listing by 100 yen (this should effectively bump the listing)
Yeah this should be possible. Once I get a chance will work on this :) .

Re: Mercari sales - Generating tax form

Posted: Wed Feb 24, 2021 4:09 am
by MajesticSoup
Viralriver wrote: Mon Feb 15, 2021 1:18 am
MajesticSoup wrote: Sun Feb 14, 2021 11:23 pm
Viralriver wrote: Fri Feb 12, 2021 7:26 am If you can be more specific with what you were wanting, I could try and whip something up when I get some free time :) . It would generally be a 'pastable' solution like what I have here. Having something run 'in the background' is quite a bit more difficult to do. There is stuff like tampermonkey/greasemonkey which can help with this, but I've always found them a pain to work with (when mixing in things like jQuery).
I was wanting to make a basic script that has this kind of logic:

- loops through all my existing listings
- if listing age > 30 days
- if listing price > 1000 yen
lower price of listing by 100 yen (this should effectively bump the listing)
Yeah this should be possible. Once I get a chance will work on this :) .
Gah I can't wait for this lol. I wish I had what it took to figure it out.

Re: Mercari sales - Generating tax form

Posted: Wed Feb 24, 2021 5:54 am
by Viralriver
MajesticSoup wrote: Wed Feb 24, 2021 4:09 am
Gah I can't wait for this lol. I wish I had what it took to figure it out.
I tried to do this for a while last week, but actually Mercari have been over-protective (for good reason) on their edit page. It basically doesn't allow me to use any normal Javascript methods to tweak the price. This does make sense, since a bad actor could easily ask you to type some code in your browser, and bam! your items now cost 1 yen each lol.

That being said, I've thought of a slightly more uglier method which might work, but just haven't had a chance to try yet. Just a quick note though, the listing age > x days isn't possible, as there is no information I can find for when the item was actually listed. If you can find that information on a page somewhere, do let me know and I can take this into account too!