IT.COM

tips Excel Tips for Domainers

Spaceship Spaceship
Watch

cocaseco

Top Member
Impact
1,351
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.
 
25
•••
The views expressed on this page by users and staff are their own, not those of NamePros.
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
 
0
•••
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.
 
1
•••
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.
 
12
•••
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:
12
•••
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.
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.
 
1
•••
@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.
 
1
•••
@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.
Awesome...I'll report back here if anything interesting comes to mind!
 
0
•••
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
 
8
•••
@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.
 
0
•••
@cocaseco yeah, it's useful. I sort my domains by this column for full control of renewals
 
1
•••
@cocaseco yeah, it's useful. I sort my domains by this column for full control of renewals

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 )
 
1
•••
Subscribed to this informative thread. Thank you @cocaseco
 
0
•••
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 )
Sure, I think I could do that. Might take me a few days until I can trim it down. PM me.
 
0
•••
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.
 
2
•••
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
 
7
•••
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.
Thanks, I can confirm it works just fine in Office 365 as well.
 
2
•••
WAAAAW Very interesting information.
Didn't know you could do so much with it.
Thnx
 
0
•••
WAAAAW Very interesting information.
Didn't know you could do so much with it.
Thnx
It's a really powerful tool, especially combined with VBA programming.
 
2
•••
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.
 
1
•••
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.

Show attachment 33334


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:

Show attachment 33345

If anyone needs more, just let me know.

Regards
Thanks. Everyone without MS Office can use free Google Sheets instead because these same commands work with it as well.
 
2
•••
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.
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
 
0
•••
Or LibreOffice if you use Linux.
 
1
•••
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.
 
2
•••
Don't be shy everyone, share your awesome excel tips
 
0
•••
Back