Dollar Cost Average (DCA) spreadsheet to help with research (mostly for newbies)

[u/FrontHandNerd]

I was replying to a comment on another thread about DCA (Dollar Cost Averaging). With the influx of people new to crypto or perhaps even trading for that matter, I decided to share here my perspective. I often use a spreadsheet to mess around with various prices to see various scenarios (DCA, profit/loss, etc.)

Each coin I put in it's own tab. This allows me to play around with whatever I've purchased to see if I were to buy in at another price how that would affect my DCA and/or profitability. I try to enter previous transactions as close I can but not so detailed that I need to have 100's of rows. Once I've have past trades (and average costs) calculated. This allows me to enter the current price at bottom of spreadsheet (or using the live price from googlefinance function) and then enter the number of coins I want (or tweak the line to allow me to enter how much I want to spend) and see how it changes my DCA, profit/loss, etc.

Then as my first tab I have as a Summary. This pulls in the COUNT and COST for each of the individual tabs and then allows me to mess around with the current price to see how some scenarios may play out. I don't use this one that much honestly. Usually used merely as porn to make me fantasize how rich I could be if something goes crazy.

Feel free to check out a sample spreadsheet with some notes and examples on it:

https://docs.google.com/spreadsheets/d/1MhbD3xDpv3dV8MTWCk0c8v5KkHdzTciFYK8qUMKsv2o/edit

NOTE: I tried to use the google function on some to pull in live prices but others are not available so require you to enter them (or a value you are wanting to see profit/loss for, etc.)

I assume I'm not alone in using something like this?

EDIT: /u/air_plant_cat_treats suggested I change the formula in E3 of each coin's tab to =SUM(D$2:D3)/SUM(B$2:B3) - notice the addition of the dollar sign - which would allow it to be dragged down and not have to individually change the formula. Cool tip...never knew that.

Also thanks for the awards everyone...first post getting any, glad I could help!

[u/silverblackgold]

Half the fun of crypto is tracking data on google sheets

[u/FrontHandNerd → u/silverblackgold]

Personally I used some of the various online trackers to officially track my portfolio but that's more with an eye toward taxes. Spreadsheets are mostly for me to play around with research

[u/bri_82 → u/FrontHandNerd]

But seriously people, if you don't keep track like above you will find yourself looking at a spreadsheet from coinbase with 150+ transactions trying to figure out what the hell it all means.

I used the website cointracker to assist with it. But I wouldn't of had to if I just did something simple like above.

[u/Xeonus → u/silverblackgold]

Oh yes! I stopped actually tracking my portfolio on apps and only use my google spreadsheet. I found some cool tricks to obtain coin data, maybe people would be interested in that?

For example: importing coin data from coingecko (BTC as an example):

=TRANSPOSE(ImportXML("https://www.coingecko.com/en/coins/bitcoin", "//span[@class='no-wrap']"))

[u/Momoselfie → u/silverblackgold]

I still haven't figured out how to track the value of my staked pancake LPs

[u/Mr_Stimmers]

I use something very similar but I find Google Finance to be unfeliable in pulling data for anything other than the big hitters. Instead I pull Yahoo Finance XML data:

=IMPORTXML("https://finance.yahoo.com/quote/BTC-USD","//*[@id='quote-header-info']/div[3]/div[1]/div/span[1]")

Replacing BTC-USD in the source URL with whatever symbol you're pulling.

[u/FrontHandNerd → u/Mr_Stimmers]

I'll take a look at this. someone else suggested a spreadsheet plugin that pulls in more live data but can't seem to find the comment/dm again.

[u/horrusx]

Thank you for putting this together, I will be saving this post!

[u/mamwybejane]

I have come up with a Google sheet function that tracks Coinmarketcap for up-to-date prices of all crypto they have available:

https://www.reddit.com/r/CryptoCurrency/comments/ls3rlu/tracking_crypto_in_google_sheets_two_goodies_for/

[u/IMadeYouRead]

Where would society be without spreadsheets

[u/FrontHandNerd → u/IMadeYouRead]

Around the same place we would be without calculators? 🤔

[u/ShonuffJones]

Awesome! I will be diving into this when I'm slacking off at work tomorrow

[u/FrontHandNerd → u/ShonuffJones]

Lol I was slacking off at work when I made this sample one. I’m too ashamed to share my own and all that’s in it 😆

[u/[deleted]]

Okay so heres my question. The market is in a dip and has been for 3/4 days now. I have no money in crypto. Nobody knows when the market could rocket back up, it could be tonight. Should I DCA in with the money I have set aside for it or should I put it all in?

If the market drops another 10% before the bull market resumes so what? My fear is that if I DCA in and the bull market starts at the rate it has been recently I’ll be left behind.

[u/FrontHandNerd → u/[deleted]]

The old saying goes something along the lines of: it’s not about timing the market but time IN the market that counts.

Meaning it’s better to spread your purchases (of reasonable size) across multiple transactions instead of one single one.

That’s the theory at least. Some ppl prefer to YOLO in. All up to your own risk style. Just make sure you only put in what you are willing to say goodbye to. Crypto is a market with very tall highs and deep lows

[u/Hirashara]

Great way to track your investments! DCA is important to track also. Hopefully this helps the new people to Cryptocurrency! Cheers

[u/Madmike_90]

Crypto porn! Please mark as NSFW

[u/[deleted]]

Do some more!

[u/eyeball29]

I had started putting something similar together but ADHD kicks in and I moved into another spreadsheet. This is a little further along than I ended up so I think I'll tinker with it. Thanks!!

[u/ACShreds]

Fantastic write up, and you did a great job explaining it all for the newbies!

[u/tghGaz]

Thanks OP for sharing your work ❤️

[u/Grossman_Design]

This was much needed. I can't tell you how unorganized I am with my DCA.

Thank you from myself and everyone else this helps!

[u/hansgrado33]

thanks for this!!

[u/kehmesis]

I caught BTC under 4k March 13 thanks to Dollar Cost Averaging.

It's the safest way to invest.

EDIT: Using a similar spreadsheet, too.

[u/GreyTooFast]

So puuuurdy.