8letters
Writing
- Joined
- May 27, 2013
- Posts
- 2,108
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
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