FE Dynamic Link
Jamie_Barnes
Member
Following on from the previous FE discussions....
FE Dynamic links essentially allows users to automate parts of research without ever needing to log in to the site. I've previously used the scheduling tool to queue up reports that i've then downloaded into my filing system, but I find that this method works much better and allows more scope to use the data for other purposes.
I've assumed everyone knows what tables in Analytics are (and being able to build custom tables). You need to add funds/sectors/ whatever it is you are researching to your active list, then run a table. In my example I've used the standard performance table and run it on the UK All Companies sector selecting members not self (the two people button).
Once the table is open, save it. Once the table is saved, at the very bottom of the page two new links will appear under the heading 'excel web query': Static and Dynamic. Copy the dynamic link (in chrome this is a manual click and drag copy).
In Excel, open a new worksheet and then click data on the menu bar at the top. Click on "from web".
In newer versions of excel this brings up a simple box with the space to paste URL code. The advanced option allows for further editing (this gets handy if you are handling large amounts of data) but for the time being, paste the excel dynamic link into the box. The first time this runs it might ask you to login to Analytics.
The last screen brings up the following:
Click on the table (not the document) which will bring up all the data being imported. You then simply need to click load,which will paste that data into a new sheet from A1. Or, you can choose loadto which allows for choosing where the data is pasted. The edit button is quite advanced but allows for the data to be customised and rules put in place (requires a bit of coding ability or at least familiarity with VB to really take advantage of that part).
The best and final bit!
The data is now in excel, albeit it tends spread out across very wide columns. Say this research is part of a weekly update, you now simply need to reopen this workbook a week later and click the following button below:
What this does is calls to Analytics the latest data, and automatically updates the cells in Excel. Either save over the existing workbook, or save as a new one and you have a trail of research.
Some of the uses i'm finding this really helpful for include:
- Comparing portfolio performance to benchmarks
- Monthly investment committee research
- In depth individual fund research
- Risk analysis that ties into other spreadsheets.
The main disadvantage of it is that large amounts of data will take a long time to load, but this is balanced out by the fact it also takes ages if you load from within Analytics itself. The unpretty nature of the columns is another downside, but can be solved by referencing to those cells and hiding the ugly sheets, or using the edit button and working it out from there.
Hope this helps!
Comments
That's brilliant. Thanks @Jamie_Barnes
@Jamie_Barnes this is a great feature that I had no idea about. I've just used it to create a tab in existing workbooks I have that calculate weighted average charges for an adviser's entire model portfolio range, plus it will give me the 1, 3 and 5 yr month end performance figures. This is now bang up to date each time the workbook is opened!
Also, I think that the way you save the data will determine the need to add a password. ie if you save it as public, either full access or read only, it should be available to anyone with the link.
And, if you edit the connection you can use some fairly intuitive wysiwyg buttons to make the data better (in the screen shot below I've removed the first row using the remove rows button and clicked 'use the first row as headers')
Anyway, thank you!
