NameSilo

Excel Tips for Domainers

Labeled as tips in General Domain Discussion, started by cocaseco, Oct 5, 2015

Replies:
28
Views:
3,119

  1. cocaseco

    cocaseco Top Contributor VIP

    Posts:
    1,464
    Likes Received:
    1,501
    I've been wanting to start this thread for awhile, so here goes.

    I know many of us use Excel to manage portfolios, big and small. Mine has developed over time as I learned new functions to implement. I thought it would be handy to have a central repository of those tips that anyone could use and not have to reinvent the wheel. Hopefully, there are some Excel masters that can share what they do. I do assume at least a basic level of Excel familiarity here.

    I'll start with a way to automatically separate out the TLD into its own column.

    So like probably everyone, my first column is the full name of my domain, including TLD. I realize I could do one column for name and another for TLD manually, but I like to have my spreadsheet do as much automatically as possible. The reason for having these two columns is that sometimes I want to look at full names, and sometimes I want to analyze my portfolio by TLD composition.

    To do this I use the "Right" command written like this: =RIGHT(A19,LEN(A19)-FIND(".",A19)) This formula would be in cell B19 and the full name would be in A19.

    The Excel RIGHT function extracts a substring from a string starting from the right-most character.
    The Excel LEN function returns the length of the specified string. So I am using the DOT as the starting point
    The Excel FIND function returns the location of a substring in a string. The search is case-sensitive.

    In common language, this string starts on the right of the entry and moves inward until it finds a dot. Then it captures everything in between the end and the dot and uses that data to fill the cell.


    So now, when I enter the full name in the first field, the second field is automatically populated with just the TLD.

    That's my tip, hope it helps someone....I hope others have more to share.
     
    The views expressed on this page by users and staff are their own, not those of NamePros.
  2. sin

    sin Top Member VIP ★★★★★★★★★★

    Posts:
    1,151
    Likes Received:
    135
    Cool idea for a thread, I use excel but only at a very basic level. Excel is a very powerful program, and I find these kind of tips/advice interesting
     
  3. cocaseco

    cocaseco Top Contributor VIP

    Posts:
    1,464
    Likes Received:
    1,501
    Glad you could use it. I hope others contribute as well. A really good spreadsheet is more than adequate for most people, and it's probably something they already own. I have a few more tips to share if nobody else does. I'm hoping for a resource to be built with this.
     
  4. cocaseco

    cocaseco Top Contributor VIP

    Posts:
    1,464
    Likes Received:
    1,501
    OK, I'll add another one that everyone can use. One of the things that perspective buyers always ask is the age of the domain. Of course, any good domaining spreadsheet has a column for this. But what if the spreadsheet could automatically calculate it, to the day? Well, the good news is that Excel can to exactly that.

    To calculate domain age, you will need a column for the date the domain was first registered. If you don't know it, look it up on WHOIS. Once you have the date, you are ready to go.

    We will be using the YEARFRAC command, which as the name implies, calculates fractions of a year between two dates. The first date is the date you got from WHOIS for the original registration. At this point, you could easily add another column that would have today's date in it. But Excel makes it easy and you can simply use "Todays" date. By using today's date, the calculation is automatically refreshed and showing the current age as of today.

    The formula is written like this:

    =YEARFRAC(F18,NOW(),3)

    In my example, the F18 is the cell where I have the original registration date that I got from WHOIS, that is the "start date". After the comma, I have entered the word "NOW" to mean exactly that for the "end date". Alternatively you could have another column that you wanted to set as the end date for some reason. In that case the cell entry would look like this:

    =YEARFRAC(F18,G18(),3)

    Where G18 would be the "end date". After the next comma is a 3 in my example and that number is called the basis. The basis defines how the calculation will be done and there are five options from 0 to 5. In my example the 3 means that the calculation will be the actual number of days calculated divided by 365. in some cases you may want to use the actual number of days divided by 360, the actual number of days as a percentage of total days available etc. Here are the "basis" options:

    Basis 0: US (NASD) 30/360 - default
    Basis 1: Actual/actual
    Basis 2: Actual/360
    Basis 3: Actual/365
    Basis 4: European 30/360

    If you wanted to use the European calculation, then the formula would look like this:

    =YEARFRAC(F18,NOW(),4)


    Realize that the alternative "basis" calculations are largely for different financial modeling applications and you can use a "3" for almost every other case. You can read more online if you want to understand the different "basis" options further.

    And that is how you let your spreadsheet provide current domain age every time you open the file.

    Enjoy.
     
  5. cocaseco

    cocaseco Top Contributor VIP

    Posts:
    1,464
    Likes Received:
    1,501
    Two in one day! actually I had a PM asking what column names do I use. So I will post here too. These are what I currently use. I have used more, and i have used less. It is always a work in process.

    DomainName - Self explanatory
    Name - This is the domain name, without the TLD
    TLD - Top Level Domain
    Length - Character length
    Planned Use - I have some general categories like; Hold, Sell, Brand, Develop
    CreateDate - Date the domain was first registered
    ExpirationDate - Date the current registration period ends
    Age - Age in years
    Renew - Do I plan on renewing Y or N
    Registrar - Where is it regsitered
    NS1 - Name server #1
    NS2 - Name server #2
    Price Paid - How much did I spend
    Estimate - Value from Estibot or Valuate for reference
    Asking Price - Currently listed asking price, blank if not listed
    Sales Price - What did it sell for, blank until sold
    Sales Date - When did it sell
    Profit - How much did I make

    I also use a color code system for each row. I currently have seven colors.

    Listed on BrandBucket
    Listed on Nameriffic
    Listed on Pinnacle.Domains
    Listed on SEDO BIN
    Not For Sale
    Sold
    Hosted with Website

    Lastly, I currently use 14 "sheets" which are the tabs at the bottom for the following items:

    Domain List - Master list of all my domains
    Lookup - All the date and registration data for lookups that feed the main data table
    Brand Bucket - Domains that other have sold on BB and a lot of statistical analysis on length, letters etc.
    eCommerce List - Subset of my domains that are suitable for eCommerce applications
    Adult List - Subset of adult domain names I own
    Words List - Subset of One, Two and Three-word domains I own
    Brandable List - Subset of brandable domain names that I have submitted to BB
    Expenses - Anything financial, including monthly expenses.
    Passwords - Master password list and user names for all accounts
    Ideas - Future ideas that I have not fully developed
    Current Project - Any idea that turns into a project, I save the data and results here
    Miscellaneous - Everything else
    RS Domains - A list of.RS domains I identified for a project
    Emergency - Instructions to my family if I die or become incapacitated, and how to protect and maximize the revenue. I'd hate to have them all expire because nobody knew. Maybe worse if they were sold too cheaply.


    Hope that helps you use Excel in a productive and powerful manner.

    What columns do you all use? I'd like to hear as I'm always tweaking.
     
    Last edited: Oct 8, 2015
  6. CoolBn

    CoolBn Upgraded Member Blue Account

    Posts:
    34
    Likes Received:
    13
    REALLY useful information...especially the "Emergency" sheet. The only information that I track that you haven't already mentioned is backorder and renewal dates of a few domains that I hope fall through the crack around renewal time (those not parked or being otherwise utilized.). Another useful data point lifetime sales. I'm super new, so my suggestions are just as green as I am. Thanks for your insight, I've gained tons of knowledge that will help me to cultivate my business.
     
  7. cocaseco

    cocaseco Top Contributor VIP

    Posts:
    1,464
    Likes Received:
    1,501
    @CoolBn I do track those on one of my tabs, probably misc. I don't have many and if I had more, I'd do them on their own tab.

    I have lifetime sales by summing the items with a sales date, all done by script.
     
  8. CoolBn

    CoolBn Upgraded Member Blue Account

    Posts:
    34
    Likes Received:
    13
    Awesome...I'll report back here if anything interesting comes to mind!
     
  9. NameAgency

    NameAgency Top Member PRO VIP

    Posts:
    1,450
    Likes Received:
    652
    Another version I use to calculate the age is based in the formula:
    =YEAR(TODAY())-YEAR(G1)
    "TODAY" is taken from the current computer day and it needs a column for the "creation date" (in my case is G1)

    Another useful data is the days to expire. I use the following formula:
    =H1-TODAY()
    Again, "TODAY" is the current computer day and it needs a column for the "expiry date" (in my case is H1)

    Lenght of the domain is also nice to have. I use:
    =LEN(B1)-LEN(C1)-1
    where B1 is the column for the domain (with TLD) and C1 is the column for TLD
     
  10. cocaseco

    cocaseco Top Contributor VIP

    Posts:
    1,464
    Likes Received:
    1,501
    @Primerlloc Thanks for contributing.

    Days to expire is an awesome idea and I will add that column as another safety net to avoid losing a domain for failure to renew.
     
  11. NameAgency

    NameAgency Top Member PRO VIP

    Posts:
    1,450
    Likes Received:
    652
    @cocaseco yeah, it's useful. I sort my domains by this column for full control of renewals
     
  12. Genius327

    Genius327 Top Contributor VIP

    Posts:
    883
    Likes Received:
    668
    Wow. That's amazing one. Would it be possible for you to give out the excel sheet you've been using ( without your data ofcourse )
     
  13. fuzzy

    fuzzy Established Member

    Posts:
    698
    Likes Received:
    791
    Subscribed to this informative thread. Thank you @cocaseco
     
  14. cocaseco

    cocaseco Top Contributor VIP

    Posts:
    1,464
    Likes Received:
    1,501
    Sure, I think I could do that. Might take me a few days until I can trim it down. PM me.
     
  15. vivaldi

    vivaldi Nothing personal VIP

    Posts:
    1,926
    Likes Received:
    1,615
    Solid stuff. Tried a lot of different ways to go about with this and I still like excel the best.
    Will develop my set up further a long the road, now I use:
    Domain Extension Expiry date Registrar Flippa Afternic SEDO DNS Inquiries Bid Cost Nameservers and another page for sold domains. With the marketplaces also having a color code that show where there is a BIN.

    Nothing fancy.

    Depending on your set up check out seotoolsforexcel.com for automatic WHOIS and Nameserver checkup, works for the basic extensions. Would like to build it out with new extensions but have yet to succed on win10 with that.
     
  16. Grossu

    Grossu Upgraded Member Blue Account

    Posts:
    974
    Likes Received:
    121
    Came across this thread, it's been quite long since last post here. I think many still use Excel, though. I'm adding a couple tips I find useful (valid for MS Excel 2007):

    1. Select your column with Expiration date, go to Home tab -> press Conditional formatting in Styles buttons group -> Color Scales -> select 1st color option. Names will get color marked, in a green-red range. The sooner name expires, the redder it will appear.

    upload_2016-6-22_10-47-14.png


    2. When adding some descriptive tags to the name, that should be picked from a list, add a listbox control to pick them from a pre-established list. Say, you have a column named "Planned use" (@cocaseco , your example). Select the corresponding range, in that column, go to Data tab -> Data Validation ->Data Validation - > Settings tab, select "List" option in the "Allow" field. In "Source" field, type in the list you want, separated by comma or semicolon (depends on your Excel settings). Example: Hold, Sell, Brand, Develop. Press OK. When selecting one of the cells, you will be able to pick a tag, out of your list:

    upload_2016-6-22_12-23-21.png

    If anyone needs more, just let me know.

    Regards
     
  17. vivaldi

    vivaldi Nothing personal VIP

    Posts:
    1,926
    Likes Received:
    1,615
    Thanks, I can confirm it works just fine in Office 365 as well.
     
  18. Domainstore

    Domainstore Top Contributor VIP ★★★★★★★★★★

    Posts:
    1,676
    Likes Received:
    990
    WAAAAW Very interesting information.
    Didn't know you could do so much with it.
    Thnx
     
  19. Grossu

    Grossu Upgraded Member Blue Account

    Posts:
    974
    Likes Received:
    121
    It's a really powerful tool, especially combined with VBA programming.
     
  20. WebInceptions

    WebInceptions Established Member ★★★★★★★★★★

    Posts:
    410
    Likes Received:
    461
    Don't you always get MS Access when you get Excel?

    Access is FAR better suited for the things you guys are going, and SQL code is (mostly) transportable to other platforms.
     
  21. korganian

    korganian Top Contributor VIP ★★★★★★★★★★

    Posts:
    2,799
    Likes Received:
    2,652
    Thanks. Everyone without MS Office can use free Google Sheets instead because these same commands work with it as well.
     
  22. Grossu

    Grossu Upgraded Member Blue Account

    Posts:
    974
    Likes Received:
    121
    I'd say it all depends on your needs. Excel and Access are not interchangeable:

    Excel is good for storing simple data, with comprehensive analyzing tools.
    Access is good for storing complex data, with simple analyzing tools.

    I used both, and for domaining needs, Excel is more than enough, imo.

    For the ones still choosing, here are some highlights in Excel vs Access:

    https://support.office.com/en-us/ar...nd-Excel-c435ade4-be8d-4809-81b0-b4a78b3cde3a
     
  23. stub

    stub DNStore.com PRO VIP ★★★★★★★★★★

    Posts:
    25,824
    Likes Received:
    11,045
    Or LibreOffice if you use Linux.
     
  24. vivaldi

    vivaldi Nothing personal VIP

    Posts:
    1,926
    Likes Received:
    1,615
    Google Sheets, Libre Office and Open Office might cover very simple needs. As soon as it gets just a little bit complicated all those apps fail over and over again. It's like comparing a Trabant to a Tesla.
     
  25. vivaldi

    vivaldi Nothing personal VIP

    Posts:
    1,926
    Likes Received:
    1,615
    Don't be shy everyone, share your awesome excel tips
     

Want to reply or ask your own question?

It only takes a minute to sign up – and it's free!
biix
  1. NamePros uses cookies and similar technologies. By using this site, you are agreeing to our privacy policy, terms, and use of cookies.
    Dismiss Notice
Loading...