Please make earnings csv file more useful

1. 70

   makingamarkposted 9 years ago

   I'm wondering if the HubPages Team are aware that earnings data on Amazon sales exported to a csv file is much less useful than it could be because the $ notation is a prefix to each amount rather than topping the column.
   
   That means it's impossible to sum the data using a formula.
   
   In other words the report is being treated as if it's text rather than numbers
   
   If the earnings data in number columns was treated as raw numbers then analysis of earnings could be done much more easily
   
   All that needs to happen is to include the $ notation at the TOP OF THE COLUMN in the heading and exclude it from the numerical data (ir make numbers numbers and not text). That would then allow the numbers to be added up.
   
   I'm sure not everybody analyses their data - but for those of us that do and export the CSV file into Excel - such a change would be very much appreciated

   replyreport
   1. 70

      Matthew Meyerposted 9 years agoin reply to this

      This is related to handling different data types in your spreadsheet program of choice.
      
      You can adjust the data types and way the CSV is imported and get the sums to work without any changes to the CSV.

      replyreport
      1. 70

         makingamarkposted 9 years agoin reply to this

         Only if you have entered the data as numbers and not text.
         
         When you enter the data as $123 you create a text value not a numerical value
         
         Import a csv into Excel and then try and add up the earnings column using the summation (add) formula - which is the Epsilon.  Then you'll understand what I'm getting at
         
         All the values are text values not numerical values - because every single number has been entered with a $prefix which makes it text not numbers.

         replyreport
      2. 87

         Marisa Wrightposted 9 years agoin reply to this

         If the data is exporting with a $ sign in front of it, then Excel still can't read it as a number.

         replyreport
         1. 83

            psycheskinnerposted 9 years agoin reply to this

            Except that when I export my reports to Excel, that's exactly what Excel does--it will then run any formulas I like on it.  I have no idea why your version of Excel is not doing this but it is most certainly possible. But I do have to set it not as a "number" but as "currency".

            replyreport
            1. 70

               makingamarkposted 9 years agoin reply to this

               I repeat
               
               I took the data from the last 90days (ie my first 90 days on HubPages) and imported the csv file into Microsoft Excel for Mac 2011 version 14.4.4. (ie the latest update)
               
               I included a summation formula at the bottom of the earnings column - and it returned no value
               
               I checked the data in the cells - and each and every number has a $ in front of it (ie it's treated as text and not a number)
               
               I tried changing the column to currency (ie US$) before starting this thread. It makes absolutely no difference. The formula still returns no value for the summation of the earnings.
               
               That's because the formula is unable to count the numbers as numbers because each is prefixed by a $

               replyreport
2. 83

   psycheskinnerposted 9 years ago

   Specifically, if your spreadsheet program does not do this automatically, select the column and change it to data type "currency". My copy of Excel seems to figure this out automatically.

   replyreport
   1. 70

      makingamarkposted 9 years agoin reply to this

      I've been using Excel for very many years (since the early 90s?) and I do know how to change the format!
      
      The thing is if data has been entered as text (i.e. $123) rather than numbers (i.e. 123) then changing the format makes no difference because now you have a column of text that is characterised as if it were currency - but the format does not work because the data is text not numbers
      
      In other words if the data is ENTERED a $123 then it's text
      
      If the data is entered as 123, then it can be treated in any number of ways according to the different formats available in Excel.
      
      Or to put it more simply - if you use the summation formula for the range of values covered by the earnings column then the result is a big fat "-"
      
      It can't even manage a zero because of course the data is text not numbers.

      replyreport
3. 83

   psycheskinnerposted 9 years ago

   All I can say is that my Excel can do it, ergo Excel must be capable of doing it.
   
   I can't show my numbersbecause ToS, but this is my last weeks earning exported direct to Excel with an autosum shown on the right hand column, working just fine.
   

   replyreport
   1. 70

      makingamarkposted 9 years agoin reply to this

      The headings across the top of the columns are different in the csv for AMAZON EARNINGS that I've imported.
      
      My headings are
      Earnings    Earnings Rate    Revenue    Price    Quantity    Item Url    Item Name    Seller    Date SHipped
      
      The Hubpages Earnings report csv (for Ad Programe, eBay and amazon) already has the columns totalled (although interestingly this is NOT a formula total - it's inserted as text)
      
      I don't see where you are getting your column headings from.
      
      Are you using the HubPages Amazon program?

      replyreport
4. 83

   psycheskinnerposted 9 years ago

   All the reports work the same for me but if you want Amazon specifically:
   
   
   

   replyreport
   1. 70

      makingamarkposted 9 years agoin reply to this

      You have a totally different set of headings.
      
      Are you getting this csv file from the "Amazon Sales Reports" tab

      replyreport
5. 70

   makingamarkposted 9 years ago

   Sorry - I should have been more specific in the title.
   
   The request relates to the Amazon report 90 days csv which can be found on the "Amazon Sales Report" tab on my HubPages dashboard
   
   I should like to have a proper data file with real numbers (i.e. NOT TEXT) so I can do further analysis on the data

   replyreport
6. 83

   psycheskinnerposted 9 years ago

   My habit is to get them all from the earnings page and use the drop down menu to change the source.  But if the issue is the $ prefix I think all the files are the same for that.

   replyreport
   1. 70

      makingamarkposted 9 years agoin reply to this

      Thanks for the info. That means we are accessing the report files in a different way.
      
      I'll try accessing the Amazon report via the earnings page and see what happens.

      replyreport
      1. 70

         makingamarkposted 9 years agoin reply to this

         ...and we have the answer.
         
         The columns are already totalled - however this is already inserted as a TEXT VALUE as the total is not created from a formula which sums the numbers
         
         Personally I like to verify sums!
         
         (I think you may be referring to the theory of being able to change columns to currency - which I agree exists if the data is numbers - rather than the need to do this. You would have no reason to do this for the 90 day csv file from your source as the total is provided - but maybe you didn't realise that it's a text total.)
         
         The point is that by making the earnings text rather than numbers HubPages is preventing people from taking the raw data and reordering it and summarising it in different ways
         
         In other words we are provided with a text file for our records and not a data file for further manipulation.
         
         My request is for a data file

         replyreport