Closed Thread
Results 1 to 17 of 17

Several financial transactions per row to be calculated.

  1. #1
    Registered User
    Join Date
    01-20-2018
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    87

    Several financial transactions per row to be calculated.

    The following is a conceptual (explanation of why each calculation is being performed) as well as practical explanation of what I am hoping someone can help with.

    If the conceptual part makes it confusing, please ignore and just pay attention to the practical parts.
    Or let me know, and I will try my best to rewrite it just listing transactions to be calculated only.


    I hope this is understandable.

    I'm going to attach Before and After samples.

    I hope this is not too confusing - I tried to make it clear. Please ask questions.


    I used Bitcoin (BTC) for trading/making short term investments.


    The Columns which are empty and have to be calculated are R/S (one or the other),
    AB, AC, AD.

    The info is already in the other Columns to use in the calculations.


    Starting out:
    AB5 shows the Total Amount of BTC to begin.

    AC5 shows the Cost Basis per BTC of $557 (the $ amount I paid divided by the number of BTC).

    AD5 shows the Total Value of my BTC (AB5 times AC5).


    The first trade is shown in Row 6.

    Column N shows the USD price per BTC on the day of the trade.
    Column O shows the amount of BTC traded.

    1) So the first thing to do is:

    I am selling some BTC.

    Re: the sale of the BTC - For every Row I need to figure out if I made a Gain (sold the BTC on a day when it's market value was higher than the Cost Basis of my BTC), or a Loss (sold the BTC on a day when its market value was lower than the Cost Basis of my BTC).


    N6 (Price of BTC on that day) minus AC5 (Cost basis of my BTC prior to this trade - NOTE this is on the prior Row) times O6 which is
    0.14 (Amount of BTC sold).

    If the product is a positive number - which this one is - it goes into R6.
    This Column shows the profit of selling the 0.14 of BTC to make the trade.

    In this case, I profited 15 (dollars):
    N6 (666 - Cost of the BTC that day) minus AC5 (Cost basis of my BTC prior to the sale) times O.14 (amount of BTC traded).

    So 15 goes into R6.
    Column R is for positive numbers.

    Had the product been a negative number it goes into Column S.

    2) The second thing to calculate is how many BTC I now have depending if my investment Gained or Lost [I'm not referring to the Gain or Loss on the sale of the BTC referred to above, but on an investment I made with the proceeds of the sale of the BTC previously referred to], and the new cost basis of my BTC if I had a Gain.

    So, continuing with Row 6:

    In AB5 We see I have 5.36 BTC prior to the sale.
    I am selling 0.14 BTC in O6, so I am retaining 5.22 BTC at a Cost Basis of $557/BTC = 2908.
    (5.22 times AC5 $557 = 2908 rounded).
    [I imagine I'll have to add a Column to retain this figure, which will be used in another calculation].

    My investment succeeded - in Z6 we see it returned $496 total.
    BTC that day was valued at $666 (in N6).

    So the investment returned 0.74 BTC.
    Dollar Amount Returned Z6 divided by BTC price that day N6 = 0.74 BTC

    So I retained 5.22 BTC, and am now adding 0.74 BTC to it for a total of 5.96 BTC in Column
    AB6.

    The BTC I retained had a value of $2908, and I am adding value of Z6 $496 so Value of my BTC AD6 is now $3404.

    Finally, for Column AC, my Cost Basis of my BTC is now Value of my BTC AD6 divided by Total Amount of BTC AB6 equals $571 which goes in AC6.
    _________________________________________
    ___________________________________________
    Now I will show an example when the investment loses and I receive nothing back, using Row 7:
    (it's a simpler calculation)

    P7 I sell 0.12 BTC.

    BTC is still 666 in N7 and my Cost Basis AC6 is now $571 so that day's BTC price N7 minus BTC Cost Basis prior to the sale AC6 times Amount of BTC sold 0.12 = 11 (rounded) which I put into Column R7 (it is a gain).

    Z7 shows nothing back - the trade lost all.

    AB6 I have 5.96 BTC minus P7 0.12 sold = 5.84 into Total Amount of BTC AB7.


    The cost basis hasn't changed - the pool of coins are still worth the same because I haven't gained any additional coins - I simply lost the 0.12 I withdrew from the pool of coins and sold.

    So all that has to be done is insert the previous Cost Basis from AC6 into AC7.


    Total Amount BTC AB7 times Cost Basis BTC AC7 and enter the product into Total Value My BTC AD7.

  2. #2
    Registered User
    Join Date
    01-20-2018
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    87

    Re: Several financial transactions per row to be calculated.

    Here is a Before and After Workbook:

    Please ignore the text in Rows 24 thru 32.
    Last edited by ununio; 03-21-2018 at 12:11 PM.

  3. #3
    Registered User
    Join Date
    01-20-2018
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    87

    Re: Several financial transactions per row to be calculated.

    I realize I offered the minimal amount.

    This is the first time I'm using the Commercial Services and wanted to post the work that needs to be calculated.

    If you can program this, please make a counter offer.

  4. #4
    Registered User
    Join Date
    01-20-2018
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    87

    Re: Several financial transactions per row to be calculated.

    How long does it normally take to get a response?

    It's 2.5 days thus far, and I have a feeling that people who take these commercial projects probably subscribe to this sub and keep on top of new posts.

    If no one can help me here, I'm going to do the work manually, and time is getting short.

    Am I correct that if there is no response by now there will probably not be any?
    Last edited by ununio; 03-23-2018 at 05:03 AM.

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Several financial transactions per row to be calculated.

    Hi, ununio
    Your attached file and explanation is confusing?
    1) In your excel file which columns you enter manually and which columns you need formulas.
    2) And explain the calculation process step by step for each column.
    3) Show how you get the required results (in all the required cells ) with formulas.
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Registered User
    Join Date
    01-20-2018
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    87

    Re: Several financial transactions per row to be calculated.

    Thanks for the response.

    1) As mentioned in the OP:

    "The Columns which are empty and have to be calculated are R/S (one or the other),
    AB, AC, AD.

    The info is already in the other Columns to use in the calculations."

    Also attached is a Before and After. The Before shows the info in the workbook prior to the work I need done.


    "2) And explain the calculation process step by step for each column.
    3) Show how you get the required results (in all the required cells ) with formulas"

    This sounds like asking the same thing twice.

    "3) Show how you get the required results (in all the required cells) with formulas"

    If this is different than what you are asking in #2 - Are you asking me to provide Excel formulas for each calculation?
    I don't know how to do Excel formulas.
    I can certainly explain #2 - the calculations per Column.
    Last edited by AliGW; 03-24-2018 at 12:00 PM. Reason: Unnecessary quotation removed.

  7. #7
    Registered User
    Join Date
    01-20-2018
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    87

    Re: Several financial transactions per row to be calculated.

    For #2, I can explain column by column from left to right and leave out the explanations of why it's being done.

    In the OP:

    "N6 (Price of BTC on that day) minus AC5 (Cost basis of my BTC prior to this trade - NOTE this is on the prior Row) times O6 which is
    0.14 (Amount of BTC sold).

    If the product is a positive number - which this one is - it goes into R6.
    This Column shows the profit of selling the 0.14 of BTC to make the trade.

    ...Had the product been a negative number it would have gone into Column S6."




    I could rewrite it as:

    ""N6 minus AC5 times O6 which is 0.14 .

    If the product is a positive number - which this one is - it goes into R6.

    ...Had the product been a negative number it goes into Column S.

    [Continue in like manner - e.g., N7 minus AC6 times O7. Positive result so enter in R7]"

    Would this be helpful?
    Last edited by ununio; 03-24-2018 at 12:15 PM.

  8. #8
    Registered User
    Join Date
    01-20-2018
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    87

    Re: Several financial transactions per row to be calculated.

    I guess there is no interest in this project. Unfortunately.

  9. #9
    Registered User
    Join Date
    01-20-2018
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    87

    Re: Several financial transactions per row to be calculated.

    Can someone refund the money please.

  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Several financial transactions per row to be calculated.

    Due to lack of a response, this thread is being closed and the points returned so they may be used for other questions.

    Your subscription to Commercial Services has been extended to provide you time to apply these points to other needs.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  11. #11
    Registered User
    Join Date
    01-20-2018
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    87

    Re: Several financial transactions per row to be calculated.

    Quote Originally Posted by JBeaucaire View Post
    Due to lack of a response, this thread is being closed and the points returned so they may be used for other questions.

    Your subscription to Commercial Services has been extended to provide you time to apply these points to other needs.
    Thank you, but I have no other needs, so refunding to my credit card at this point would be what I'd like.

  12. #12
    Registered User
    Join Date
    01-20-2018
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    87

    Re: Several financial transactions per row to be calculated.

    It's been 10 days.

    I just checked my credit card statement and see no refund.

    Please confirm that you have received this message and are making the refund.

    Thank you.

  13. #13
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Several financial transactions per row to be calculated.

    Hi Ununio, sorry but I did miss this followup message.

    No promises on the refund, that's not something I can recall being done. But I'll certainly send your request to the forum owners and explain your request, I will do what I can to get it approved and done for you.

  14. #14
    Registered User
    Join Date
    01-20-2018
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    87

    Re: Several financial transactions per row to be calculated.

    "No promises on the refund, that's not something I can recall being done."

    So if I had deposited $100, which IIRC is one of the deposit options, the forum would have kept the $100?

    How can that be called anything other than a ripoff, regardless of the amount deposited, if no one accepts the offer.

    The forum does not state "Deposit up to $100, and if no one accepts the job, we keep the money."
    Who in their right mind would deposit any amount under those terms?

    I was very impressed with this forum and the helpful people here, but this commercial services plan is ridiculous.

    I will be initiating a chargeback to my credit card by tomorrow.
    Last edited by ununio; 05-06-2018 at 02:49 AM.

  15. #15
    Registered User
    Join Date
    01-20-2018
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    87

    Re: Several financial transactions per row to be calculated.

    Before I deposited, here is the response to my asking about the refund from forum moderator dominicb:

    Quote Originally Posted by ununio View Post
    "If a deal is never made, do I get the $10 refunded back to my cc?"

    "If someone doesn't take your post up, yes."

    https://www.excelforum.com/suggestio...ml#post4869880

    He clearly is aware of refunds, which you were never aware of apparently per your post above.

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Several financial transactions per row to be calculated.

    Request has been sent to management regarding this, I will keep you updated
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Several financial transactions per row to be calculated.

    I know this is way way late, but mngt have said the money was refunded - can you confirm, please?

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. difference between calculated field and calculated column in PowerpIvot
    By stephme55 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-14-2016, 06:05 PM
  2. Replies: 22
    Last Post: 09-20-2013, 02:00 PM
  3. Replies: 2
    Last Post: 09-17-2013, 07:12 PM
  4. Replies: 1
    Last Post: 03-04-2012, 02:37 AM
  5. Replies: 2
    Last Post: 03-06-2009, 11:49 AM
  6. Replies: 3
    Last Post: 06-06-2006, 12:10 AM
  7. Are financial functions calculated based on compound interest?
    By KDR in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-02-2005, 11:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1