Spreadsheets



  • Hi guys. Started to take my trading more seriously so wondered if anyone has any tips about what they use a spreadsheet for/what data they include in it? Any suggestions/help appreciated.



  • Mugs game 😉



  • I set up a spreadsheet with a list of all the players and use the RAND() function to help lookup a random player.
    I keep hitting F9 until the name Aubameyang pops up and then I buy some more.
    Player pump, brought to you in association with MS Excel.

    Sorry OP I know that isnt especially helpful.

    If you want to pay £4 a month I would recommend IndexEdge. Raw data downloads available there plus ready made spreadsheets with solid analysis.



  • @chaps1988 said in Spreadsheets:

    Hi guys. Started to take my trading more seriously so wondered if anyone has any tips about what they use a spreadsheet for/what data they include in it? Any suggestions/help appreciated.

    Things to track....

    • Reasons you bought the player
    • What you think might go wrong
    • What your exit strategy is
    • How much you expect to win and by when

    All the other info you need like the date you bought the player, can be pulled from FI's spreadsheets easily enough.

    Personally, I don't bother.... but I make posts on here almost every time I buy a player, listing my reasons, what I think the exit points will be... so I have something to refer to from time to time.



  • @chaps1988 there's a free downloadable one I use on here
    https://www.footballtradinginsights.com/trading-checklists



  • @Westy is there a mac version?



  • @chaps1988

    4330fdca-9419-409e-830d-9b26b226421a-image.png
    1fd86009-07c7-480d-ac9a-be18f6dc8467-image.png
    b4337f33-a948-4973-80d7-c94f777da852-image.png
    8ceef9e9-8670-4ae4-9644-75468487f1e6-image.png
    58c1cc88-6c84-4427-b6b5-993c8897dc23-image.png
    d807d346-ec1d-409f-8ed1-ab4d2b42280d-image.png

    So i recently created this bad boy (currently this is the 6th iteration of my spreadsheet), hopefully you can see the column headings so you can see what sort of data i collate on there if you want to make one yourself.

    Granted it looks like the amount of work involved is horrific but it is really not so don't let that put you off. It is basically a hybrid of the footy index scout spreadsheet which is downloadable and very good and the info that can be cut an pasted from this free price alert website here https://indexpricealerts.com/login So all you do it set your port up on the site (do this first) then copy data (one click) and paste into excel and then the rest of the info such as total p/l on each player, total port value, total IS value all of that good stuff auto calculates for you including the different colors, red and green are if its gone up or down or in profit or not profit, the gold colour comes on when the IS spread is <5% and the avg buy price goes green when it is less then the IS price (so no typing in numbers from the FI website anymore, that used to be a right pain, now it just takes seconds to update and see whats going on, Bliss!) The info regarding divs etc (in baige) all collates from various tabs, not automatic you will have to input the info in yourself, but its easy enough to do.
    I added extra tabs to collate together all those annoying cash/marketing bonuses FI give out (like when they did that double div promo, half of which was paid out in cash bonus half in proper divs) and also a new one for TOTM so it will count how many times your players win it and how much. There is an IPD tracker tab as well, put your players in and the purchase date and it will tell you how many days of IPDs you have left, then in the other table you put the player details in and any IPDs earn't will populate in the main table. So this spreadsheet separates out all the divs won into MB/PB/IPD/TOTM. I also added a spaces where you can calculate your IRR and an area to record your daily port value, deposits, cash and total divs which will then auto plot onto a graph so you can literally watch your port grow which is fun!!

    I am sure there is other stuff you could add as well

    If you or anyone else would like a copy i would be happy to dropbox it, Strongly recommend keeping a spreadsheet especially any newbies out there, i would recommend doing it sooner rather then later so that its not too much of an issue to upload your data before you transaction history becomes unmanageable.

    Only catch is that you will have clear all my data out first, so you can put in yours but all the equations and conditional highlighting are there, its mainly the divs that you have won over your time on the index that take a while to input initially but once its done its done (not that the info you get from the price alert site all comes written in lower case, you will need to do this with plaer names in the dividend tabs as well ie put all in lower case, otherwise the sheet will not work) and then its just a case of updating as you go, but if that's cool then your welcome.

    Link here if anyone wants. https://www.dropbox.com/s/3nuysiuhribiun9/FI Portfolio V6.xlsx?dl=0



  • @chaps1988 I record the obvious stuff like price bought, sold and dates. Then I also record every IPD, MB, PB, TOTM and CA (when sold) won by every player.

    On a separate page I record all dividends won per week and the number of wins, running totals and averages.

    On a separate page I record my IRR (more accurate than ROI) which includes every deposit and withdrawal date.

    On the first of each month I record my portfolio value and summaries all of the above (deposits, withdrawals, bonuses, CA, MB wins, PB wins etc).

    Finally for a bit of fun I recently sold 500 Neymar and put this into the ME to top up 17 different players. I am now tracking the dividends and CA these17 players generate Vs what 500 Neymar would have won to see if I made the right decision at the right time.

    I like my numbers and love my spreadsheet!!



  • @chaps1988 Depends on exactly what you want to do, I use numbers on the iphone its free.

    The details I recomend that you keep that are a must is

    1. When you bought the shares!!!!!!! Cant stress this enough.
    2. How much you bought them for
    3. How much you sold them for + div wins so you can work out profit/loss.

    Some added bonus's would be the FI itself and your port % going up and down, div wins, pie chart to see % in each player etc .thumbnailww.jpg

    The middle blue chart is my profit, I like that one lolaa.jpg



  • @howsthebacon no just that one for excel sorry



  • Thanks for all this guys, very useful. Some of it does seem a lot of unnecessary work given it can be found on FI or external sites anyway. What's the reasons for keeping track of dividends won? Why not just overall profit?

    Hopefully FI will add share expiry dates to the platform as that's clearly something they should have on there - any ideas on this?



  • @MickTurbo said in Spreadsheets:

    Mugs game 😉

    FOYC. 🐴😜



  • This is my effort which i set up a couple of months ago one night when i was pissed
    I came down stairs and had forgotten to switch my PC off and there is was staring at me.
    The colours were enough to trigger a bout of projectile vomiting all over the dog, tbf, there were a lot more reds on the screen that day . I suspect there will be even more tommorow....

    I download the data from Index Price Alerts which updates live from FI.

    Having used this for a couple of months now, it has prepared me in someway for the drops i expect to see tomorrow, as well as how volatile the index will be especially in the short term.

    I expect that my port will settle at around the mid price range once everything settles down which is roughly 91% of the full "Paper" value it shows currently.

    I believe @MickTurbo has a framed picture of it which takes pride of place on his mantelpiece at home

    24301af0-e616-43de-bd37-feb9b1b60642-image.png



  • @Chris-C I don't understand how your portfolio profit on investment is more than your deposits, given you deposited £3528 but have invested £4209??

    Capture.PNG



  • @chaps1988 I'm just guessing the difference is due to him re-investing his dividends.



  • @chaps1988 Yeah your totally correct mate, I think that i just made an error in the equation, which i have just corrected.

    And yeah my investment is more then my deposits as any dividends and profit from player sales i re-invest into the portfolio.

    Sorry about that guys, my bad

    new link to the corrected spreadsheet

    https://www.dropbox.com/s/v3pnr1c3l0oyiaj/FI Portfolio V6.xlsx?dl=0



  • I've been tracking my Portfolio religiously for the last 2 years on a spreadsheet. The main daily input of data takes less than a minute and gives a good overview of performance and helps me stay calm through periods of decline. Like most that have posted here, I would recommend time series graphs and yield projections

    70e537d2-17ea-4857-b2ad-ec38379330ab-image.png

    dea122dc-6a53-411c-a383-187a90b2bc9a-image.png

    ec2a92b5-2a59-48af-8939-64ac0647b5e7-image.png

    6c633092-3317-4fd7-b21b-32cf384f1042-image.png

    59a9b4ba-0438-4463-a7b9-4be4f504bbb0-image.png

    1ee0f779-cfc9-4669-a6e3-b911171d7ed0-image.png



  • IndexGain 3rd party product does have a pretty good portfolio management view/reports. But I do also record my portfolio less frequently

    076e7acc-3e36-4d39-9746-307e92c04feb-image.png

    Helps with tracking and diversifying my purchases

    750a2b8b-8031-4cec-89c2-ba2c1daeca10-image.png

    7bdfefa1-3d17-4f49-8f0a-6b18c0e705b7-image.png

    9e0a5cfb-83f5-42ca-bb49-4eae43d32bec-image.png

    f9c4ae0e-2364-47e9-9dbf-285447fa2df7-image.png

    7cabc64d-e0b8-4e9a-8dbf-76a8fac72dfc-image.png



  • And finally, I find it useful to update all historic transactions fromFI. I update this every couple of weeks

    a332a793-1ea1-4e3a-86cb-b290279fdc5b-image.png

    You could pivot this data to see which players are inplay for IPDs. Useful when you have a massive portfolio (200 players)

    8f174a77-4403-4bf0-a2df-c96905539de1-image.png



  • @Parvez112333 how do u work out your annual yield? The % youve got for 2019 of 87.9% is confusing me considering you invested around 52k and made 28.7k profit...

    All good stuff though, helping me a lot with my accounts, thanks lads.


Log in to reply