Hello!

A discussion on rav revealed that some designers would like to export data from Ravelry Pro to manipulate in excel and in particular, create a chart of Purchases by Month. Here I go through the steps to work up the chart yourself, and at the bottom of the page is a link to a dummy excel file you can use. If you use the dummy file, you can just pop your Ravelry Pro exported data into the Purchases tab, but going through the steps will help you figure out how to to it yourself and maybe get different data out.

1. Export the data from your purchases page
   http://www.ravelry.com/stores/XXX/purchases

   

2. Then open that in a new excel document. The date of purchase column is column A.
3. You will need to use text-to-columns on column A to turn it into date format as just converting the data to “date” won’t work. Text-to-columns is on the data tab in my version (2010). Rename this excel sheet “Purchases”.
4. On a new excel sheet, set up 2 columns as follows
   

   A	B
   Month	Purchases
   01/01/2010

    

5. Call this sheet “Purchases per Month”. (January 2010 is an example. Best to use your first month with sales.)
6. Put the following into cell A3, then copy and paste into the rest of column A until it shows December 2014. This formula adds one month per cell.
   =DATE(YEAR(A2),MONTH(A2)+1,DAY(A2))
7. Put the following into B2 and then copy and paste it into the rest of the cells below it. This formula basically says “count all purchases where
   1. The “paid” column is not empty AND
   2. The date is greater than or equal to the first of the month AND
   3. The date is less than the first of the next month

   =COUNTIFS(Purchases!F$2:Purchases!F$10000,”<>”,Purchases!A$2:A$10000,”>=”&A2,Purchases!A$2:A$10000,”<“&A3)
   *Please note, this last formula only works up to 10,000 purchases, but OTOH, if you have more than 10,000 purchases, I can, for a small (teeny, weeny) fee, write a spreadsheet for you!
   **Sorry it runs off the page, but I don’t want to add spaces cos that will break the formula if the spaces are pasted too.

8. And then to make the chart, just select the data in columns A and B on the “Purchases per Month” sheet and click on the barchart from the Insert tab.

Finally, here, for your downloading pleasure, is the mock_purchases spreadsheet.

If you use the spreadsheet, you will need to do the Text-to-columns thing in step 3) everytime you copy in the data from rav.