jaizan wrote:
Question:
If I have an excel document containing a sheet with all trades ever and another sheet with all dividends ever, what's the best way of configuring it to, on request, automatically update a third table with trades & dividends in date order, which will be used for XIRR etc..?
Given that you've got a working set of processes for importing these various data-sets from your existing brokers, I think rather than trying to create a third
combined data-set with which to work out any XIRR calculations, it might be worth spending a bit of time on a
copy of your current spreadsheet, and see if you can get XIRR working on the data as it stands...
Without seeing your current data, it's difficult to give any specific advice here, so I thought I'd knock something generic up in Excel and let you have a play to see if it might be useful...
If you open a new Excel workbook, and create the following cash-flow dataset, we can hopefully verify that a
single-column data-set of the following cash-flows gives us a
base-line Excel XIRR of 11.44% -
So with that as our default XIRR position, we can then start to split the cash-flow data out into
two separate sets of data, to perhaps mimic your personal '
broker-data' situation, and see if we can
maintain the 11.44% XIRR result...
Underneath the first data-set, create the following data configuration, splitting the share cash-flow away from the dividend cash-flow -
Note that the above XIRR formula uses an Excel VBA function called '
arrayunion', which we need to create in Excel VBA first, to enable the automatic joining of the two data-sets.
This can be easily done by opening the VBA editor in Excel, creating a new workbook '
module', and then pasting the following code into that VBA module (
note the 'Select all' button at the top of the following window, to help with Copy and Paste of the VBA code) -
Code: Select all
Function ArrayUnion(ParamArray Arg() As Variant) As Variant
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
For i = LBound(Arg) To UBound(Arg)
Arg(i) = Arg(i)
If IsArray(Arg(i)) Then
For Each Itm In Arg(i)
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
ArrayUnion = TempUnion
End Function
Hopefully at that stage, you've been able to show how the XIRR function
can be used with split data, although I do suspect that there may be a reliance on compatible
date-formats within each of your own separate broker-related data-sets, and this is why, if you can satisfy yourself that the above example works for you on a new scrap spreadsheet, then it might be worth you then having a play with the above '
combined XIRR' process using
A COPY of your own broker data...
Whilst the above basic XIRR examples have been carried out on the same demo-worksheet, there's no reason why the selected ranges of data can't be taken from
different worksheets, of course...
If this were me, and this solution was something that I was keen to investigate, then I'd most probably pull out a couple of
small sections of each broker-related data-set (shares and dividends), and mimic the above
single-data-set-XIRR process to enable a base-line XIRR value to be obtained again (
manually generating a small, combined single-column data-set again, for base-line proving purposes from your own data...), and then also mimic the above multi-column solution
with that small data-set, and
verify the above multi-column XIRR process using that
small sub-set of your own broker-drawn data, and that should hopefully allow some confidence to be gained for the next important improvements if it looks to be coming together favourably...
So the next improvements you might make on the above process, if you're happy with it, might be to start looking at creating
dynamic named-ranges of your broker data...
Dynamic Named Range -
A dynamic named range expands automatically when you add a value to the range.
https://www.excel-easy.com/examples/dyn ... range.html
Why I mention dynamic named ranges is for two reasons -
1. Named ranges can be used in your XIRR formula, to describe each of the four sections of broker data - for example -
Stock_Data ,
Divi_Data ,
Stock_Dates ,
Divi_Dates
2. Creating the above '
Dynamic Named Ranges' on your imported broker-data sheets should then allow your XIRR calculation to then
automatically take those subsequently expanded lists of broker-data into account
each time you import additional data onto those specific sheets, as demonstrated on the '
Dynamic Named Range' link above...
(Please note - all of the above Excel images are my own screen-shots)
Hopefully there's something in the above to have a play with and think about.
Hope it's useful.
Cheers,
Itsallaguess