TheBeCitizenList [Beta 1.0]

by **Reaussou** 2010-05-21, 10:12

Link: http://users.skynet.be/fa205218/CitizenList.xlsm
YOU NEED TO ENABLE MACROS

How does it work
The excel lists all pages from this list: http://www.erepublik.com/en/rankings/citizens/country/1/32
So what info does that contain? A rank, a nickname and XP. What can we do with that info? If we compare the data collected from two different points in time we can see the following:

Individual Increase in Rank
Individual Increase in XP
Individual Leavers
Individual New Players
Collective Amount of People allowed to vote (Don't know if it's still correct with the new formula, can edit it)
Collective Amount of New People
Collective Amount of Inactive People
Level Demography
Average Level

Working with it (Office 2007)

optional In cell G1 copy the date from cell A1
optional In cell A1, write today's date.
Press 'Copy'
Go to sheet Web and right cell C1; Edit query; Log in on that page in eRep.
Open tab data in between formulas and review; Refresh All; Refresh All
Wait 15 minutes. Visit Reaussou's profile and donate all your currency, gold, houses and Q5 weapons. Just kidding.
Done!

Now what's the problem I can't get the sheet out of beta? I can only add the queries up to the page that has people listed (currently page 145). So technically every time you reopen the sheet and want to refresh it you need to add the new queries. This is not hard, but how do you do this, since it's not automated yet.

Go to sheet Web and go to the last data in the C column. In the second blank cell under it do the following.
In tab Data in between Formulas and Review you click on From Web.
URL: http://www.erepublik.com/en/rankings/citizens/country/XXX/32 ; In the URL change the XXX to the page that's not been queried yet. So the list goes to 1450 players, the next query should be page 146. Change XXX to 146.; Press Go; Click the little yellow arrow on the web page left by the list; Press Import. (You do this until no new pages can be added by increasing the XXX by one each time)

The next problem is that the pages I collect my data from are updated in a very strange way. Some people only end up on it after their 5th day being on eRep, others end up on it on their first. So the new player data is somewhat off but it does work.

Appreciation, donations, hate/love mail, spam and election propaganda is welcome in my mailbox.
Reaussou

P.S.: Vote Res Belgica.

TheBeCitizenList [Beta 1.0] Rb1000