Donate to Remove ads

Got a credit card? use our Credit Card & Finance Calculators

Thanks to johnstevens77,Bhoddhisatva,scotia,Anonymous,Cornytiv34, for Donating to support the site

Excel Question - Combining Data

Discussions regarding financial software
jaizan
Lemon Slice
Posts: 386
Joined: September 1st, 2018, 10:21 pm
Has thanked: 213 times
Been thanked: 113 times

Excel Question - Combining Data

#494941

Postby jaizan » April 18th, 2022, 10:34 am

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)

Google points me to sites that offer add ins that cost more than my Excel license.

Darka
Lemon Slice
Posts: 773
Joined: November 4th, 2016, 2:18 pm
Has thanked: 1819 times
Been thanked: 704 times

Re: Excel Question - Combining Data

#494958

Postby Darka » April 18th, 2022, 11:40 am

You don't need addin's for this, learn the following functions and practice with them on a simple set of tables until you understand them.

INDEX and MATCH
INDEX and XMATCH

INDEX and either MATCH or XMATCH when used together are very powerful, some examples from my own spreadsheet.

Looking up when I first bought a share from a table of transactions.
=INDEX(Transactions[Date],XMATCH(Portfolio!C17,Transactions[EPIC],0,-1))

Adding up all the dividends for a specific SHARE owned by me in a specific BROKER account
=SUM(SUMIFS(Transactions[[Total ]], Transactions[Broker], "Hargraves Landsdown", Transactions[Transaction], {"Dividend","Dividend_Special"}, Transactions[EPIC], "LGEN", Transactions[Owner], "Darka"))

Or you could probably do something with pivot tables, but I never liked those as I found them too restrictive.

Or, you could merge both of your tables into one, which is what I have - a single table for all transactions, including dividend receipts.

GoSeigen
Lemon Quarter
Posts: 4350
Joined: November 8th, 2016, 11:14 pm
Has thanked: 1590 times
Been thanked: 1579 times

Re: Excel Question - Combining Data

#494963

Postby GoSeigen » April 18th, 2022, 11:53 am

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)

Google points me to sites that offer add ins that cost more than my Excel license.


Stupid question, but why not put them all into one table in the first place and then filter the entries to view either only trades or only dividends?


GS

NotSure
Lemon Slice
Posts: 916
Joined: February 5th, 2021, 4:45 pm
Has thanked: 679 times
Been thanked: 314 times

Re: Excel Question - Combining Data

#494966

Postby NotSure » April 18th, 2022, 12:14 pm

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)

Google points me to sites that offer add ins that cost more than my Excel license.


Going 'backwards' may be easier - that is, maintain the trade + dividend table, then (automatically) extract the other two separately from it?

mc2fool
Lemon Half
Posts: 7812
Joined: November 4th, 2016, 11:24 am
Has thanked: 7 times
Been thanked: 3017 times

Re: Excel Question - Combining Data

#494975

Postby mc2fool » April 18th, 2022, 12:43 pm

Well, a simplish and brute force approach would be just to record a macro to do it. Once-only create the Merged worksheet then start recording a macro (Tools->Macro) and:

go to the Merged sheet
select entire sheet
Delete
go to the Trades sheet
select entire sheet
Copy (ctrl-c)
go to the Merged sheet
click on A1
Paste (ctrl-v)
press ctrl-downarrow and then downarrow to get to the first empty cell
go to the Dividends sheet
select entire sheet
Copy (ctrl-c)
go to the Merged sheet
Paste (ctrl-v)
select entire sheet
Sort by the column with the date in (if you want; actually the data given to XIRR can be in any date order, it doesn't have to be sorted)

Stop recording. Now you can just invoke the macro whenever you add anything to Trades and/or Dividends.

Note, the above assumes you have the relevant data in the same columns in both Trades and Dividends, and in any case, unless you have the amounts suitably signed (positive/negative) for XIRR in the source sheets, you'll have to do some massaging of the data anyway ... probably adding an extra column with the correctly signed amount, which can also be done by the macro....

There are definitely more elegant approaches, but using a macro is simple-ish. :D
Last edited by mc2fool on April 18th, 2022, 12:48 pm, edited 1 time in total.

jaizan
Lemon Slice
Posts: 386
Joined: September 1st, 2018, 10:21 pm
Has thanked: 213 times
Been thanked: 113 times

Re: Excel Question - Combining Data

#494976

Postby jaizan » April 18th, 2022, 12:46 pm

GoSeigen wrote:Stupid question, but why not put them all into one table in the first place and then filter the entries to view either only trades or only dividends? GS


I've got about 6 broker accounts and get numerous dividend payments per year. That's too much to maintain manually, so I periodically download the dividend transactions to Excel, so one sheet for each broker account.
So I want to then combine them into one Excel sheet for dividends, with a standardised order.

Then after, that I want to combine dividends and trades, so I can calculate XIRR. I already have an Excel spreadsheet that does this, except I last updated the dividends about a year ago. So I now need to add a lot more data. Rather than do lots of cutting, pasting and sorting, I want to automate this as much as possible. How to automatically append the new data from the individual broker dividend records is the challenge.

Itsallaguess
Lemon Half
Posts: 9129
Joined: November 4th, 2016, 1:16 pm
Has thanked: 4140 times
Been thanked: 10023 times

Re: Excel Question - Combining Data

#495020

Postby Itsallaguess » April 18th, 2022, 4:11 pm

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% -

Image

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 -

Image

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/dynamic-named-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...

Image

(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

jaizan
Lemon Slice
Posts: 386
Joined: September 1st, 2018, 10:21 pm
Has thanked: 213 times
Been thanked: 113 times

Re: Excel Question - Combining Data

#495044

Postby jaizan » April 18th, 2022, 5:47 pm

Itsallaguess wrote: 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...


The existing spreadsheet already calculates XIRR for any ticker. I just type the Ticker in the box and it does that, but it's using the combined dataset with all trades and dividends in one sheet. I even had a little Macro to work through a Ticker list and put the XIRR for each stock next to the ticker.
The problem is the manual process to combine everything into that one sheet.

If you have an example of using XIRR to calculate using data on 7 different worksheets, I'd think about it.

However, as it stands, when I download the dividends from 6 different brokers, into Excel, I would like to automate combining those 6 sets of data and the trades, on a 7th sheet. This needs to be appended as well, since the brokers don't make it easy to download over 20 years of data in one go.

Itsallaguess
Lemon Half
Posts: 9129
Joined: November 4th, 2016, 1:16 pm
Has thanked: 4140 times
Been thanked: 10023 times

Re: Excel Question - Combining Data

#495053

Postby Itsallaguess » April 18th, 2022, 6:51 pm

jaizan wrote:

However, as it stands, when I download the dividends from 6 different brokers, into Excel, I would like to automate combining those 6 sets of data and the trades, on a 7th sheet.

This needs to be appended as well, since the brokers don't make it easy to download over 20 years of data in one go.


I'd have a go to see if you can combine the imported data into a single sheet, and then configure that sheet to also pull out the necessary granular data you need for any subsequent XIRR calculations on the same sheet, given that it's likely that the different brokers will be presenting the required copied data in different ways to you.

By way of an example as to how this might be done for three different broker data-sets, I've created the following data -

Image

In the above example, Columns G, H, and I are representing your 'pasted' data from your brokers, where I'm assuming somewhere in the imported data there is a mixed-up representation of the data you're actually interested in, so for example, the Dividend Amounts happen to be in Column H for Broker A, Column I for Broker B, and Column G for Broker C, etc...

As you paste each 'section' of data from the relevant brokers into such an 'Overall Data' worksheet, you would then manually enter the 'Broker' marker in Column B as you did each 'pasted section'.

Then, to the right of the 'Broker' marker, you would then drag down a 'Nested IF' formula for each of Columns C, D, and E, which would then 'pull out' the relevant 'Company', 'Date', and 'Dividend' fields from the pasted broker-data to the right of it -

Company Formula (Entered into C5, and dragged down as appropriate)

Image

Date Formula (Entered into D5, and dragged down as appropriate)

Image

Dividend Formula (Entered into E5, and dragged down as appropriate)

Image

(Please note - the above Excel images are my own screen-shots)

Carrying out an XIRR process on your configured Columns D and E would then perhaps give a much quicker and smoother operation than having individual sheets for each broker, I would hope, and would still enable you to filter on particular brokers to see their specific data using Column B to do so, if required....

Cheers,

Itsallaguess

OldPlodder

Re: Excel Question - Combining Data

#495116

Postby OldPlodder » April 19th, 2022, 7:57 am

Unlike Darka, I find pivot tables very useful indeed and use them extensively.

In fact you can do the job the OP is talking about with two pivots, placed well spaced out below each other. Plus one entry way below for current value at today’s date. To the right, you just extract the data required by XIRR, ie ignoring the headers.

I know it works because this is how I create a generic framework to get XIRR from any EPIC I hold. You just have to select the Epic twice, the only manual entry required. You can duplicate as many times as you like, I have half a dozen at a time, but in fact you can do it for the whole portfolio if required. I have had this working well for many years. Before that I did the job with a large macro, but these days I have practically given up writing code, too much bother ( wrote too many million lines of code while at work!)

Plodder

jaizan
Lemon Slice
Posts: 386
Joined: September 1st, 2018, 10:21 pm
Has thanked: 213 times
Been thanked: 113 times

Re: Excel Question - Combining Data

#495124

Postby jaizan » April 19th, 2022, 8:43 am

Itsallaguess wrote:I'm assuming somewhere in the imported data there is a mixed-up representation of the data you're actually interested in, so for example, the Dividend Amounts happen to be in Column H for Broker A, Column I for Broker B, and Column G for Broker C, etc...


I plan to deal with the problem of columns in the wrong order either by having one sheet in the original broker layout and another sheet in a "Standard" layout, with the data copied to the Standard sheet either by using a simple formula or a HLOOKUP function.

The problem remaining is how to automatically combine the data from 7 sheets with standard layout to one sheet.

TonyB
Posts: 44
Joined: November 8th, 2016, 10:38 am
Has thanked: 1 time
Been thanked: 9 times

Re: Excel Question - Combining Data

#496268

Postby TonyB » April 25th, 2022, 8:30 am

Power Query is Excel's software for mashing data together from a whole range of sources and works very well, takes a little bit of getting used to but well worth the effort. It's available on the Data tab of recent Excel versions.

There are quite a few good YouTube introductory videos. Hers' one with a whole series on Power Query:

https://www.youtube.com/watch?v=wOVMJXf ... c&index=34

TonyB

servodude
Lemon Half
Posts: 8271
Joined: November 8th, 2016, 5:56 am
Has thanked: 4435 times
Been thanked: 3564 times

Re: Excel Question - Combining Data

#496279

Postby servodude » April 25th, 2022, 9:02 am

jaizan wrote:
Itsallaguess wrote:I'm assuming somewhere in the imported data there is a mixed-up representation of the data you're actually interested in, so for example, the Dividend Amounts happen to be in Column H for Broker A, Column I for Broker B, and Column G for Broker C, etc...


I plan to deal with the problem of columns in the wrong order either by having one sheet in the original broker layout and another sheet in a "Standard" layout, with the data copied to the Standard sheet either by using a simple formula or a HLOOKUP function.

The problem remaining is how to automatically combine the data from 7 sheets with standard layout to one sheet.


Big meta consolidated sheet using RANK against the date of entry as a sort field?

Many moons ago I gave up using excel for dynamic data and slithered off to python for anything like this...


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: No registered users and 7 guests