Getting story stats into Excel

8letters

Writing
Joined
May 27, 2013
Posts
2,106
Takes about a minute to dump all of the stats on the submissions page into Excel. Won't get the number of favorites.

Let's start by creating a new spreadsheet in Excel and then naming three tabs "Source", "Snapshot" and "All Data".

Next, let's get some data into Excel to work with. Go to your View Submissions page and save it as an htm or html file. How you do that is browser dependent and may take a bit of looking around to figure out how to do it. Once that's saved, open that file in Excel. Highlight the columns A through G and select Copy (Ctrl + C). Go to the "Source" tab and Paste Values. We've got some data now.

Go to the "Snapshot" tab. This is where we going to put the story statistics in one row per story. * In cell A1, put the formula "=18+7*ROW()"
* In cell B1, put the formula "=MONTH(NOW())&"/"&DAY(NOW())&"/"&YEAR(NOW())" <= Non-Americans will have to change this to match their default date format
* In cell C1, put the formula "=INDEX(Source!A$1:G$64500,Snapshot!A1,2)"
* In cell D1, put the formula "=VALUE(INDEX(Source!A$1:G$64500,Snapshot!A1+1,2))"
* In cell E1, put the formula "=VALUE(INDEX(Source!A$1:G$64500,Snapshot!A1+1,3))"
* In cell F1, put the formula "=VALUE(INDEX(Source!A$1:G$64500,Snapshot!A1+1,4))"
* In cell G1, put the formula "=VALUE(RIGHT(INDEX(Source!A$1:G$64500,Snapshot!A$1+2,2), LEN(INDEX(Source!A$1:G$64500,Snapshot!A1+2,2))-17))"

In that first row, you should have the stats for your first story submission. Copy that row and paste it downwards for each story you have beyond 1. If you publish a new story, you'll need to copy and paste a new line for that story.

In the "All Data" tab, put in the columns of the first row the following values - "Date", "Story", "Score", "Views" and "Comments".

Save the spreadsheet.

We're ready now to get the story stats into Excel. Steps are:
1. Go to your View Submissions page
2. Save it as an htm or html file
3. Open that file in Excel
4. Highlight columns A-G
5. Copy
6. Go to the "Source" tab in your story stats spreadsheet
7. Paste Values into cell A1
8. Go to the"Snapshot" tab
9. Select cells B1 to the bottom right
10. Copy
11. Go to the "All Data" tab
12. Go to the first empty row
13. Paste Values
 
This is very thorough and impressive, and thank you for posting it. :)

Now I just need to find a remedial Excel course... :eek:
 
Thanks, but what's the point?

Maybe you should write a how to and submit that.
 
This is very thorough and impressive, and thank you for posting it. :)

Now I just need to find a remedial Excel course... :eek:

I know. I was all impressed with myself that I figured out the formula to merge columns the other day,
 
I know. I was all impressed with myself that I figured out the formula to merge columns the other day,

Teach me, oh wise one. :)

Edit: I just remembered -- that's actually one I know! It's the formulas I have problems with.
 
=(INDIRECT("c"&ROW())/(INDIRECT("d"&ROW()))*100)

Percent of votes per view in mine. I'm sure there's an easier way to do it, but I handled it the same way I handle all coding problems. Beat it with a fuckin' hammer until it works.
 
=(INDIRECT("c"&ROW())/(INDIRECT("d"&ROW()))*100)

Percent of votes per view in mine. I'm sure there's an easier way to do it, but I handled it the same way I handle all coding problems. Beat it with a fuckin' hammer until it works.

Or leave out the "*100", select the range, and use Format -> Number -> Percentage (or whatever it is, don't have the exact sequence memorised).

The "multiply by 100" approach can lead to errors if you're doing further arithmetic with those numbers: e.g. 100% * 100% should be 100%, not 10000%. Using the "format" approach makes it easier to get the right answers.
 
Takes about a minute to dump all of the stats on the submissions page into Excel. Won't get the number of favorites.

Let's start by creating a new spreadsheet in Excel and then naming three tabs "Source", "Snapshot" and "All Data".

Next, let's get some data into Excel to work with. Go to your View Submissions page and save it as an htm or html file. How you do that is browser dependent and may take a bit of looking around to figure out how to do it. Once that's saved, open that file in Excel. Highlight the columns A through G and select Copy (Ctrl + C). Go to the "Source" tab and Paste Values. We've got some data now.

Go to the "Snapshot" tab. This is where we going to put the story statistics in one row per story. * In cell A1, put the formula "=18+7*ROW()"
* In cell B1, put the formula "=MONTH(NOW())&"/"&DAY(NOW())&"/"&YEAR(NOW())" <= Non-Americans will have to change this to match their default date format
* In cell C1, put the formula "=INDEX(Source!A$1:G$64500,Snapshot!A1,2)"
* In cell D1, put the formula "=VALUE(INDEX(Source!A$1:G$64500,Snapshot!A1+1,2))"
* In cell E1, put the formula "=VALUE(INDEX(Source!A$1:G$64500,Snapshot!A1+1,3))"
* In cell F1, put the formula "=VALUE(INDEX(Source!A$1:G$64500,Snapshot!A1+1,4))"
* In cell G1, put the formula "=VALUE(RIGHT(INDEX(Source!A$1:G$64500,Snapshot!A$1+2,2), LEN(INDEX(Source!A$1:G$64500,Snapshot!A1+2,2))-17))"

In that first row, you should have the stats for your first story submission. Copy that row and paste it downwards for each story you have beyond 1. If you publish a new story, you'll need to copy and paste a new line for that story.

In the "All Data" tab, put in the columns of the first row the following values - "Date", "Story", "Score", "Views" and "Comments".

Save the spreadsheet.

We're ready now to get the story stats into Excel. Steps are:
1. Go to your View Submissions page
2. Save it as an htm or html file
3. Open that file in Excel
4. Highlight columns A-G
5. Copy
6. Go to the "Source" tab in your story stats spreadsheet
7. Paste Values into cell A1
8. Go to the"Snapshot" tab
9. Select cells B1 to the bottom right
10. Copy
11. Go to the "All Data" tab
12. Go to the first empty row
13. Paste Values

I employ people to do stuff like this - I'm told it gives them a reason for living.:devil:
 
The nested brackets in that formula gave me fits for quite some time.

That number is never reused, and I wanted a formula I could use in the entire column without having to specify the exact cells. Every time I add a new story, it's the same formula, rather than having to edit it for each row. Paste and done.

When I do the overall, I take it from the sums of the votes and views column, ( already calculated for display ) rather than utilizing the percentage of column.

Or rather, would if I was writing anything or bothering to track my numbers any longer. It seems pretty pointless when I haven't finished anything since January, and don't foresee that I'll put anything new out any time soon, if ever.

Or leave out the "*100", select the range, and use Format -> Number -> Percentage (or whatever it is, don't have the exact sequence memorised).

The "multiply by 100" approach can lead to errors if you're doing further arithmetic with those numbers: e.g. 100% * 100% should be 100%, not 10000%. Using the "format" approach makes it easier to get the right answers.
 
Back
Top