There must be an easier way!

[peterbatah]

Hello,

My client put together this worksheet which helps him track his investment portfolios. I made a few transaction entries myself and found the process to be counter intuitive at best. If there is an easier way I would appreciate your assistance. I can provide more info if required. Thank you.

Please see attached files for examples.


peter 2017 08 22 16 08 11 - YouTube

Excel File:

Dropbox - peter.xlsm

Transaction data:

Screenshot by Lightshot

 

[peterbatah]

Perhaps that I should have mentioned that the unit price provided by the insurer is only 4 digits in length (2 decimal places) eg. 13.16. However, I need the unit price to be up to 7 digits in length (5 decimal places)

for example, using your code if I were to enter 16.65 in column D, I would see 16.65000 in column D, and 39.93994 in column E
I require that column D display 16.64702 and 39.94709 in column E.

By the way I am using Microsoft Excel 2016 MSO (16.0.7726.1049) 64-bit. Office 365 subscription

 

[MARK858]

First of all the error with Jeanie is because you are using 64bit Excel which isn't supported.

As for the code I am not sure whether the discrepancy is because of bankers rounding (which VBA uses) or floating point precision (and can't really be bothered to test).

Then test for the same results.

Try the code below which basically just puts the formula into column E, Calculates it and converts to values.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Not Intersect(Target, Range("D3:D" & Range("A" & Rows.Count).End(xlUp).Row)) Is Nothing Then
        Application.EnableEvents = False
        With Target.Offset(, 1)
            .FormulaR1C1 = "=IFERROR(RC[-2]/RC[-1],0)"
            .Calculate
            .Value = .Value
        End With
        Target.Offset(, 2) = Target.Offset(-1, 2).Value2 + Target.Offset(, 1).Value2
        Application.EnableEvents = True
    End If

End Sub

Except, in order to achieve desired results, I would have to enter total units in column D first, take the number in col E then manually punch it in to column D.

In your video what formula is in E7 when you type 171.48780 (obviously I am not going to retype your data from the video to try and work it out), it seems like you need an extra column to do this calculation first as I don't see the point of typing in a cell then copying it back the result rather than using another column.

 

[peterbatah]

First of all the error with Jeanie is because you are using 64bit Excel which isn't supported.
Understood. Thank you. I could always work on another PC running a 32-bit version if absolutely necessary.

In your video what formula is in E7 when you type 171.48780

The formula used in E7 is =IFERROR(C7/D7,0). Keep in mind that our current modus operandi is to enter 171.48780 into D7 first, cursor right then F9. No formula exists in D7.

I don't see the point of typing in a cell then copying it back the result rather than using another column.

Which is what I was hoping to avoid all along

 

[MARK858]

So basically the below gives the correct result in F7

Excel Workbook
	A	B	C	D	E	F
1	Date	Transaction	Deposit/Withdrawal	Unit Price		Units bought/sold
6						166.37717
7	16-Apr-16	PAC	$ 2,500.00	171.4878	14.57830	171.48776
Sheet3

As does the below in E7

Excel Workbook
	A	B	C	D	E
1	Date	Transaction	Deposit/Withdrawal	Unit Price	Units bought/sold
6					166.17717
7	16-Apr-16	PAC	$ 2,500.00	171.4878	171.48776
Sheet2

If the results are correct I need to know which suits best and also whether the last macro gives the "correct" end results based on your previous workbook.
Btw, not working on this anymore tonight

 

[peterbatah]

Column D should end up with the unit price as seen in the video eg. D3 = 13.69780. But, I must enter the total unit in D3 first in order to determine the unit price because I have mentioned before the insurer only provides the unit price in this format eg. 13.69.

The video shows exactly how we have been making our entries and what the results must be.

And this is what information we begin with.

Screenshot by Lightshot

 

[MARK858]

D3 wasn't affected by your formula and so I didn't do anything with it (and can't unless you have a table or similar to do a lookup for the actual value).

 

[peterbatah]

D3 wasn't affected by your formula and so I didn't do anything with it (and can't unless you have a table or similar to do a lookup for the actual value).

If I am not explaining myself properly, I apologize.

The whole point of the formula is to yield the desired result in column D. Sorry if I sound like I am repeating myself but the insurer provides us with the following information as seen in the screenshot that I provided in a few posts:

This is an example of a single transaction on a specific date

Date of the transaction = Date entry not used in calc
Transaction = Text entry not used in calc
Deposit/Withdrawal = used in calc
Total units = 171.487800 used in calc
Unit price = 14.58 not used in calc but must be 7 digits long and 5 decimal places and should look like 14.57830 once formula is complete and is shown in column D


Perhaps you were right in suggesting that a helper cell is required.

 

[MARK858]

I get the 14.57830 as a result when I type 171.4878 in D7 in the first table I posted in post number 14 except the 14.57830 is now in column E because of the helper column with the formulas provided below the table.

Did you try it?

You can't type in a cell and have a formula in the same cell which is why if doing it with formula you need a helper column

Easy enough to calculate the cell with out you turning on calculation with VBA and with the helper column.

I can probably write some VBA to get the result in column D without the helper column but I need you to make sure that you are getting the right results with the formula version before I spend time attempting it.

 

[peterbatah]

With the formula that you provided in post #14 should I be seeing these results. I realize that some of the values are located in the wrong cell at the moment is that correct. I simply added =G2+F3 for example in column G so that we can tally up the Total Units

Screenshot by Lightshot

 

[MARK858]

In the file in the link below do you get the correct results if you type 171.4878 in D7 in each sheet.

Obviously in the formula version the results will be 1 column over.

https://app.box.com/s/w6v1eatcacqcf4qoa1pb2sjauz1n0exk