Mekoides
Old Alpha
- Joined
- Nov 24, 2006
- Posts
- 951
- Location
- Chicago, IL, USA
- Society
- Pyrates of Calypso
- Avatar Name
- Mekoides Tux Deathbringer
I've developed a new spreadsheet application for calculating auction profits, and I thought I'd share it with the community. It has an easy to use data-entry interface and allows the user to quickly calculate profits of a potential transaction.
The reason I developed my own little app instead of trying to use one already out there, is because I couldn't find one that I liked that would calculate auction fees, allowed me to set my own purchase and sale price, and allowed for the calculation of bulk trades.
This should work with any version of MS Excel 2000 and newer.
http://www.mediafire.com/?d7fjx5vhwbyvr13
If anyone has any suggestions for improvement, please let me know.
The spreadsheet opens in a pretty simple view
[br]Click to enlarge[/br]
Clicking the "New Entry" button brings up the data entry page - all fields have tool tips describing what belongs where. The "MU Type" dropdown menu allows the user to differentiate between the two markup types - percentage and TT+
Clicking the "Add Row" button will take all of the entered (and calculated) data and add it to a new row in the spreadsheet, allowing the tracking of multiple items within a single trade.
[br]Click to enlarge[/br]
For those interested, this is the entire source code - very simple data entry/calculation functions. I'd be happy to explain any of it if anyone has questions.
The reason I developed my own little app instead of trying to use one already out there, is because I couldn't find one that I liked that would calculate auction fees, allowed me to set my own purchase and sale price, and allowed for the calculation of bulk trades.
This should work with any version of MS Excel 2000 and newer.
http://www.mediafire.com/?d7fjx5vhwbyvr13
If anyone has any suggestions for improvement, please let me know.
The spreadsheet opens in a pretty simple view
[br]Click to enlarge[/br]Clicking the "New Entry" button brings up the data entry page - all fields have tool tips describing what belongs where. The "MU Type" dropdown menu allows the user to differentiate between the two markup types - percentage and TT+
Clicking the "Add Row" button will take all of the entered (and calculated) data and add it to a new row in the spreadsheet, allowing the tracking of multiple items within a single trade.
[br]Click to enlarge[/br]For those interested, this is the entire source code - very simple data entry/calculation functions. I'd be happy to explain any of it if anyone has questions.
Code:
Private Sub CommandButton1_Click()
UpdateValues
Range("A1").End(xlDown).Offset(1, 0).Select
If (ItemDescription.Value = "") Then
ItemDescription.Value = "No Description"
End If
ActiveCell.Value = ItemDescription.Value
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = TTValue.Value
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = AucPrice.Value
ActiveCell.Offset(0, 1).Activate
If (ItemType.Value = "%") Then
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = PctMUPaid.Value * 0.01
Else
ActiveCell.Value = TTMUPaid.Value
ActiveCell.Offset(0, 1).Activate
End If
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = "=(ROUNDDOWN(C" & ActiveCell.Row & ",0))-B" & ActiveCell.Row
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = "=(ROUNDDOWN(C" & ActiveCell.Row & ",0))/B" & ActiveCell.Row
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = "=IF(ROUNDDOWN(0.5+F" & ActiveCell.Row & "*99.5/(1990+F" & ActiveCell.Row & "),2)<0.5,0.5,ROUNDDOWN(0.5+F" & ActiveCell.Row & "*99.5/(1990+F" & ActiveCell.Row & "),2))"
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = "=IF(D" & ActiveCell.Row & "<>0,IF(E" & ActiveCell.Row & "=0,C" & ActiveCell.Row & "-(D" & ActiveCell.Row & "+B" & ActiveCell.Row & ")-H" & ActiveCell.Row & ",),IF(E" & ActiveCell.Row & "<>0,C" & ActiveCell.Row & "-E" & ActiveCell.Row & "*B" & ActiveCell.Row & "-H" & ActiveCell.Row & ",F" & ActiveCell.Row & "-H" & ActiveCell.Row & "))"
End Sub
Private Sub ItemType_Change()
If (ItemType.Value = "%") Then
MULabel.Visible = True
PctMUPaid.Visible = True
TTMUPaid.Visible = False
TTMUPaid.Value = 0
Label8.Visible = False
Label7.Visible = False
Label9.Visible = True
MUPCT.Visible = True
Label13.Visible = True
Label14.Visible = True
End If
If (ItemType.Value = "TT+") Then
MULabel.Visible = True
TTMUPaid.Visible = True
PctMUPaid.Visible = False
PctMUPaid.Value = 100
Label8.Visible = True
Label7.Visible = True
Label9.Visible = False
MUPCT.Visible = False
Label13.Visible = False
Label14.Visible = False
End If
UpdateValues
End Sub
Private Sub PctMUPaid_Change()
If (PctMUPaid.Value < 0) Then
PctMUPaid.Value = 100
End If
If (PctMUPaid.Value > 9999999) Then
PctMUPaid.Value = 0
End If
UpdateValues
End Sub
Private Sub SpinButton6_Change()
UpdateValues
End Sub
Private Sub SpinButton2_Change()
UpdateValues
End Sub
Private Sub SpinButton3_Change()
UpdateValues
End Sub
Private Sub SpinButton4_Change()
UpdateValues
End Sub
Private Sub SpinButton5_Change()
UpdateValues
End Sub
Private Sub TTMUPaid_Change()
UpdateValues
End Sub
Private Sub TTValue_Change()
If (TTValue.Value = ".") Then
TTValue.Value = "0."
End If
UpdateValues
End Sub
Private Sub UserForm_Initialize()
ItemType.AddItem "TT+"
ItemType.AddItem "%"
End Sub
Private Function UpdateValues()
Dim truncate As Double
If TTValue.Value < 0 Then
TTValue.Value = 0
End If
If TTValue.Value > 99999 Then
TTValue.Value = 0
End If
AucPrice.Value = (SpinButton6.Value - 50) + (SpinButton5.Value - 50) * 10 + (SpinButton4.Value - 50) * 100 + (SpinButton3.Value - 50) * 1000 + (SpinButton2.Value - 50) * 10000
MUPed.Value = AucPrice.Value - TTValue.Value
If (TTValue.Value > 0) Then
truncate = (AucPrice.Value / TTValue.Value * 100) * 100
truncate = Int(truncate)
MUPCT.Value = truncate / 100
End If
If ((0.5 + MUPed.Value * 99.5 / (1990 + MUPed.Value)) < 0.5) Then
AucFee.Value = 0.5
Else
If ((0.5 + MUPed.Value * 99.5 / (1990 + MUPed.Value)) > 99.5) Then
If (TTValue.Value > AucPrice.Value) Then
AucFee.Value = 0.5
Else
AucFee.Value = 99.5
End If
Else
truncate = (0.5 + MUPed.Value * 99.5 / (1990 + MUPed.Value)) * 100
truncate = Int(truncate)
AucFee.Value = truncate / 100
End If
End If
If (ItemType.Value = "%") Then
If (TTValue.Value > 0) Then
truncate = (AucPrice.Value - ((PctMUPaid.Value * 0.01 - 1) * TTValue.Value) - TTValue.Value - AucFee.Value) * 100
truncate = Int(truncate)
Profit.Value = truncate / 100
End If
End If
If (ItemType.Value = "TT+") Then
If (TTValue.Value > 0) Then
Profit.Value = AucPrice.Value - TTMUPaid.Value - TTValue.Value - AucFee.Value
End If
End If
End Function
Last edited:
and VBA since I was in college 
