Help with Excel and SL Transactions for biz please?

I'd like to be able to generate relatively simple summary reports for my inworld biz based on my SL transactions, downloaded in Excel format.

I'd like to show the total revenues for each product, without having to do lots of finicky editing each time I download a new data set.

I suspect that there's a good way to do this in Excel. I'm not an expert, but I do know the basics and how to make charts. Given a brief outline and a few threads to pull on, I'm sure I can connect the dots. So, just asking for a couple clues or approaches here.

Thanks!

BTW, if I come up with anything generically useful, I'll be happy to share it.

Hmmm....

The only thing you can upload into SL is a texture. So.....if you generate any kind of chart, it's going to have to be then saved in one of the formats that SL allows as an upload......bmp, png......are two for sure.

As for using Excel and then translating the data......not entirely sure how you would go about that. I know for a text upload.....you essentially have to use the text tool in a program like photoshop or gimp.....so it can uploaded into SL as a 'texture' or 'image'.

For business purposes.....it might be easier for you to have your data posted on a web site and use the link in-world, either in your profile or at your business, to connect people to the web site. That's the only thing I can offer........but if anyone else out there knows of some tricks....I'm sure they will be along to share~!!

I might be wrong but I don't think he wanted to upload it into world, I use Excel (sorry I don't know how to do graphs etc.,) I can find that information by sorting for it, which I'm sure someone will point out is the hard way

Here's how to do it in Excel 2003:

1) Download the transactions.

2) Click the little box in the corner of the two header rows, to select the whole sheet.

3) Data > Filter > AutoFilter.

4) Pull down the "Type" filter and set it to "Payment".

5) Data > Sort. Sort by "Description" and leave the rest of the form as it is.

6) Data > Subtotals. At each change in "Description" use function "Sum" and add subtotal to "Credit".

7) Ta-daa!

Lear

If thats all you want to do then search the 'sumif' option in excel. What that basically does is searches all column/cells in a range and adds all those together that meet your given criteria. Take a look at the examples in the help file from excel and you will see how it works.

If its something more complexed then post back with a better description and I can give you more examples of formulas to use.

Since the transaction history is only good for 30 days I'd like to be able to merge files saved from multiple months, remove duplicate entries and generate lists of who owns which product. I'm sure this is simple for someone who knows Excel, but...I don't.

You can do some pretty spiffy things with macros. You could just import a new unedited transaction worksheet, hit a "process" button, and it would automatically add all that data into another worksheet containing data on "revenue by product by month with totals" and "objects for sale by owner". It could autogenerate all your graphs with the updated information, and check the transaction ID to avoid duplicates. Graphs on "currency spent by individual" (see who your top customers are)? Could do all kinds of things... I don't own an in-world business, but I like mucking with things like that. I have to imagine someone somewhere has already done this for themselves though.

Sweet. I didn't know about "sumif". Thanks Larrie. I learn something new every day.

Is there any way to take a range and extract a list of all distinct values within that range?

I thought of using sumif, but the problem is, I think you'd have to manually make a list of products first - unless there's a way to do that just from the Description column.

Thanks, everyone!

So far, sumif looks like a great tool, whether I use it for this or not. I think Yumi is correct that it would require either writing a macro to compile all the product names. I don't write Excel macros, so I'm not sure quite what they're capable of.

I did take a moment and wrote a python script to do what I want. I just have to save each excel spreadsheet in CSV format, and I can run it on any or all of them (e.g., *.csv). It does filter out duplicate entries by ID, so if I have overlapping data it doesn't distort the results.

Let me know if you'd find it useful and I'll dust it off a bit and post it.

It prints a list of product names (ignoring my version suffixes) and for each product gives the quantity and total revenues. It also prints total revenues and the date range covered by all the CSV files. The list is sorted in descending order of revenues.

Now, if I'd recorded the number of hours spent per product, I could really learn something useful. As it is, it's fun to see what's popular, and what generates the most revenues.

oooh Something like that would be even better than bling!

(OK, just kidding about the bling.)

When you say "import a new unedited worksheet, hit a 'process' button ...", do you mean use "Data -> Import external", or what? And would this 'process' button be on an existing worksheet?

Python is fun, but it would be nice to do this all in Excel, esp with its graph generating capability.

the best way to do this in my mind with with a pivot table...it can compile totals for different items into a numerical table. then you can copy that table to another spreadsheet and edit it to make it look pretty or you can make it into a chart. making a pivot table is not toooo complicated but best to use the help function in excel to learn it...the steps and variables are too many for me to outline them here.

Oryx, (Hi ya, long time no speak)

just remember that 'sumif' can only have 1 criteria/argument. This can be generated from a pre-created list or typed directly in the formula.


Yes, but its very difficult to give a specific example or formula without knowing exactly what it is you are looking to generate or calculate.

'sumif' could work but this will depend on what (as you mentioned) the distinct values are. The only options would be a one criteria meets all or creating a list.

If however you are refferring to the transactions excel spreadsheet then yes you could generate a list using a formula without having to filter everything or alternatively you could create a Pivot Table from it.

Well... you could use "Data > Import external data", or do a worksheet copy too... Maybe a bit more elegant of a solution would be to have a folder on your computer designated for the transaction files and just download them all to there. Your macro could check the folder for new files to process and pull the data in. The "Process" button would be on a permanent spreadsheet where all your summary data resides. However... you could also get rid of the "process" button and have the spreadsheet just check for new data when it launches, that way it's all transparent to the end-user.

*shrug* I do this stuff all day long. Maybe I'll dink with something and see if anyone finds it useful when I'm done. No promises though.

Macros are written using VBA (Visual Basic for Applications).

What they do is record keystrokes and mouse clicks to simplify for example laborious tasks.

If you select tools-macro-record new macro, play around with some stuff on a spreadsheet then stop the macro recording you can view it what it recorded using VBE (Visual Basic Editor) also found in the tools menu under macros.

OK, but would I have to redo all the steps every time I downloaded a new spreadsheet of data from the SL website? That's what I want to avoid, if it's more than a few manual steps. I'm a lazy summabitch, and repetitive tasks are what computers are for, anyway!

You would but its literally a few clicks....once you do it and get it the way you like it the first time...make sure you write down the configuration and its a snap to do again. edited to add: with the kinda heavy duty scripting you can do this will be nooooo problem...its literally drag and drop some buttons into a layout that is provided...no code writing or formulas to do etc.

Perhaps the poster of /327/2f/178090/1.html#post1474147
could help if they are still around. Suzi Sohmers.

I wonder what the easiest way to automated downloading the transaction history would be. AutoIt?

What about having something other than "object pays" -- and list the actual object's name that is paying out? That would be extremely helpful for me.